本文主要介绍SQL的子查询语句。
====================================================================================================================================
查询中嵌套查询的形式即为应用SQL子查询的形式。
<pre name="code" class="sql">SELECT table1.field1, table2.field2 FROM table1 NATURAL JOIN table2 WHERE field3 = (SELECT ……) #上述形式即为使用了SQL的子查询功能 “SELECT table1.field1, table2.field2 FROM table1 NATURAL JOIN table2 WHERE field3 =”称为外层查询 #“(SELECT ……)”为内层查询 #当然如下形式也是子查询的应用 SELECT table1.field1, (SELECT ……) AS fied2 FROM table1 NATURAL JOIN table2;
- 非关联子查询
若子查询可以独自运行,且不需要引用外层的查询结果,则该子查询为非关联子查询。
SELECT table1.field1, (SELECT field2 FROM table2) AS field2 FROM table1 NATURAL JOIN table2; #该查询中,其子查询部分未引用和外层查询相关的内容,所以为非关联子查询
- 关联子查询
关联子查询值内存查询的解析过程中需要引用外层查询的结果。
SELECT t1.field1, (SELECT field2 FROM t2) AS field2 FROM table1 AS t1 NATURAL JOIN table2 AS t2; #该查询中,其子查询部分引用和外层查询相关的内容(“SELECT field2 FROM t2” t2是在外层查询里解析的),所以其为关联子查询。 #关联子查询通常用来寻找外层查询中不在关联结果中的项,常与“NOT EXISTS”搭配。 SELECT t1.field1, t2.field2 FROM table1 AS t1 NATURAL JOIN table2 AS t2 WHERE t1.field3 =/>/</<=/>=/NOT EXISTS/EXISTS (SELECT t2.field3 FROM t2) ;