sql语句子查询大全

left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录
right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录
inner join(等值连接) 只返回两个表中联结字段相等的行

举例如下：
--------------------------------------------
表A记录如下：
aID　　　　　aNum
1　　　　　a20050111
2　　　　　a20050112
3　　　　　a20050113
4　　　　　a20050114
5　　　　　a20050115

表B记录如下:
bID　　　　　bName
1　　　　　2006032401
2　　　　　2006032402
3　　　　　2006032403
4　　　　　2006032404
8　　　　　2006032408

--------------------------------------------
1.left join
sql语句如下:
select * from A
left join B
on A.aID = B.bID

结果如下:
aID　　　　　aNum　　　　　bID　　　　　bName
1　　　　　a20050111　　　　1　　　　　2006032401
2　　　　　a20050112　　　　2　　　　　2006032402
3　　　　　a20050113　　　　3　　　　　2006032403
4　　　　　a20050114　　　　4　　　　　2006032404
5　　　　　a20050115　　　　NULL　　　　　NULL

（所影响的行数为 5 行）
结果说明:
left join是以A表的记录为基础的,A可以看成左表,B可以看成右表,left join是以左表为准的.
换句话说,左表(A)的记录将会全部表示出来,而右表(B)只会显示符合搜索条件的记录(例子中为: A.aID = B.bID).
B表记录不足的地方均为NULL.
--------------------------------------------
2.right join
sql语句如下:
select * from A
right join B
on A.aID = B.bID

结果如下:
aID　　　　　aNum　　　　　bID　　　　　bName
1　　　　　a20050111　　　　1　　　　　2006032401
2　　　　　a20050112　　　　2　　　　　2006032402
3　　　　　a20050113　　　　3　　　　　2006032403
4　　　　　a20050114　　　　4　　　　　2006032404
NULL　　　　　NULL　　　　　8　　　　　2006032408

（所影响的行数为 5 行）
结果说明:
仔细观察一下,就会发现,和left join的结果刚好相反,这次是以右表(B)为基础的,A表不足的地方用NULL填充.
--------------------------------------------
3.inner join
sql语句如下:
select * from A
innerjoin B
on A.aID = B.bID

结果如下:
aID　　　　　aNum　　　　　bID　　　　　bName
1　　　　　a20050111　　　　1　　　　　2006032401
2　　　　　a20050112　　　　2　　　　　2006032402
3　　　　　a20050113　　　　3　　　　　2006032403
4　　　　　a20050114　　　　4　　　　　2006032404

结果说明:
很明显,这里只显示出了 A.aID = B.bID的记录.这说明inner join并不以谁为基础,它只显示符合条件的记录.
--------------------------------------------
注:
LEFT JOIN操作用于在任何的 FROM 子句中，组合来源表的记录。使用 LEFT JOIN 运算来创建一个左边外部联接。左边外部联接将包含了从第一个（左边）开始的两个表中的全部记录，即使在第二个（右边）表中并没有相符值的记录。

语法：FROM table1 LEFT JOIN table2 ON table1.field1 compopr table2.field2

说明：table1, table2参数用于指定要将记录组合的表的名称。
field1, field2参数指定被联接的字段的名称。且这些字段必须有相同的数据类型及包含相同类型的数据，但它们不需要有相同的名称。
compopr参数指定关系比较运算符："="， "<"， ">"， "<="， ">=" 或 "<>"。
如果在INNER JOIN操作中要联接包含Memo 数据类型或 OLE Object 数据类型数据的字段，将会发生错误.

 

一、有 = <> > < >= <= 的子查询
格式：
SELECT col1 , col2 , col3
FROM tb
WHERE col = [ANY | SOME | ALL](
SELECT coln FROM tb WHERE ...
)

举例：
SELECT `ID`, `classname`
FROM `ydfzx_software_class`
WHERE `fatherid` = ANY(
SELECT `id`
FROM `ydfzx_software_class`
WHERE `fatherid`=13
)

在这个查询中，子查询返回一个离散值(一列一行)，如果有ANY或ALL的修饰，可以返回一个离散值列表（一列多行） ,其实 col = ANY 就相当于col IN


二、IN 子查询
格式：
SELECT col1 , col2 , col3
FROM tb
WHERE col [NOT] IN(
SELECT coln FROM tb WHERE ...
)

在有IN谓词的这个子查询中，返回一个离散值列表（一列多行）



三、SELECT ROW 字查询，注意返回值是0或1
格式：
SELECT ROW(value1,value2,value3 ...) = [ANY | SOME] SELECT col1,col2,col3

如果第2条查询必须返回一组离散值（多列单行），如果有ANY和SOME谓词的修饰，返回一组离散值列表
举例：
SELECT ROW(1,'操作系统'，1) = ANY (SELECT `ID`,`caption`,`status` FROM `ydfzx_software_class`)
在`ydfzx_software_class`里查找有没有`ID`=1，`caption`='操作系统',`status`=1的记录，有就返回1，没有就返回NULL 或 0


四、EXISTS 子查询
格式:
SELECT col1,col2 FROM a WHERE EXISTS(SELECT ...)
解释:
主查询先查询出数据,再逐条通过EXISTS子查询验证,只有子查询返回的记录数不为0时,主查询中的记录有效.这个查询开销比较大

users表
┏━━━┯━━━┯━━━━┓
┃ uid │ name │address ┃
┃ 1 │张荣 │中光 ┃
┃ 2 │晨曦 │fuyang ┃
┃ 3 │川湘 │中光 ┃
┃ 4 │张荣 │浙江 ┃
┗━━━┷━━━┷━━━━┛
xl表
┏━━━┯━━━┯━━━━┓
┃ uid │ xl │year ┃
┃ 1 │大专 │2007 ┃
┃ 1 │大本 │2008 ┃
┃ 1 │硕士 │2009 ┃
┃ 2 │小学 │2012 ┃
┃ 2 │初中 │2018 ┃
┃ 2 │高中 │2021 ┃
┃ 2 │大本 │2025 ┃
┃ 4 │大本 │2025 ┃
┃ 5 │大本 │2025 ┃
┃ 3 │大专 │1995 ┃
┃ 3 │大本 │2001 ┃
┗━━━┷━━━┷━━━━┛


举例：找出users表中那些在xl表中有xl=小学的记录，他们的共同键为id
SELECT *
FROM `users`
WHERE EXISTS (
SELECT *
FROM `xl`
WHERE `xl`.`uid` = `users`.`uid` AND xl = '小学'
)
等效于:
SELECT `users`. *
FROM `users` , `xl`
WHERE `xl`.`xl` = '小学' AND `users`.`uid` = `xl`.`uid`

得到的结果为:

┏━━━┯━━━┯━━━━┓
┃ uid │name │address ┃
┃ 2 │晨曦 │fuyang ┃
┗━━━┷━━━┷━━━━┛



五、衍生数据表子查询
格式： SELECT ... FROM (SELECT ...) AS name WHERE 。
首先执行子查询生成临时表，外层查询再到此临时表中获取数据。
举例：
SELECT `users`.`uid` , `users`.`name` , count( `xl`.`uid` ) AS lcount
FROM `users` , `xl`
WHERE `users`.`uid` = `xl`.`uid`
GROUP BY `users`.`uid`
ORDER BY `lcount` DESC

等效于：
SELECT *
FROM(
SELECT `users`.`uid` , `users`.`name` , count( `xl`.`uid` ) AS lcount
FROM `users` , `xl`
WHERE `users`.`uid` = `xl`.`uid`
GROUP BY `users`.`uid`
) AS `temp`
ORDER BY `lcount` DESC

有一存储树形目录的表tb, 共有3个字段，ID，classname,fatherid,每个节点一条记录.ID字段为非重复字段，classname为此节点的名称,每个子节点的fatherid等于其父节点的ID，如果fatherid为0，表示它为顶层节点，如何能一次就查询

出各顶层节点有没有子节点呢，经过调试，以下语句执行成功。

SELECT `ID` , `classname` , `fatherid` ,

IF(
(SELECT COUNT(`ID` )
FROM `tb`
WHERE `fatherid` = `main`.`ID`
) >0, 1, 0) AS `haveson`

FROM `tb` AS `main`
WHERE `fatherid` =0

2、一次性查询出某节点的兄弟节点
SELECT *
FROM `tb`
WHERE `fatherid`=(SELECT `fatherid` FROM `tb` WHERE `ID`=6)

3、一次性查询出某节点的父辈节点
SELECT *
FROM `tb`
WHERE `fatherid`=(
SELECT `fatherid`
FROM `tb`
WHERE `ID`=(SELECT `fatherid` FROM `tb` WHERE `ID`=13)
)

exists 和 in

in适合内外表都很大的情况，exists适合外表结果集很小的情况。

http://blog.itpub.net/category/385/14461

exists 和 in 使用一例
===========================================================
今天市场报告有个sql及慢，运行需要20多分钟，如下：
update p_container_decl cd
set cd.ANNUL_FLAG='0001',ANNUL_DATE = sysdate
where exists(
select 1
from (
select tc.decl_no,tc.goods_no
from p_transfer_cont tc,P_AFFIRM_DO ad
where tc.GOODS_DECL_NO = ad.DECL_NO
and ad.DECL_NO = 'sssssssssssssssss'
) a
where a.decl_no = cd.decl_no
and a.goods_no = cd.goods_no
)
上面涉及的3个表的记录数都不小，均在百万左右。根据这种情况，我想到了前不久看的tom的一篇文章，说的是exists和in的区别，
in 是把外表和那表作hash join，而exists是对外表作loop，每次loop再对那表进行查询。
这样的话，in适合内外表都很大的情况，exists适合外表结果集很小的情况。

而我目前的情况适合用in来作查询，于是我改写了sql，如下：
update p_container_decl cd
set cd.ANNUL_FLAG='0001',ANNUL_DATE = sysdate
where (decl_no,goods_no) in
(
select tc.decl_no,tc.goods_no
from p_transfer_cont tc,P_AFFIRM_DO ad
where tc.GOODS_DECL_NO = ad.DECL_NO
and ad.DECL_NO = ‘ssssssssssss’
)

让市场人员测试，结果运行时间在1分钟内。问题解决了，看来exists和in确实是要根据表的数据量来决定使用。

not in <>not exists

请注意not in 逻辑上不完全等同于not exists，如果你误用了not in，小心你的程序存在致命的BUG：

请看下面的例子：
create table t1 (c1 number,c2 number);
create table t2 (c1 number,c2 number);

insert into t1 values (1,2);
insert into t1 values (1,3);
insert into t2 values (1,2);
insert into t2 values (1,null);

select * from t1 where c2 not in (select c2 from t2);
no rows found
select * from t1 where not exists (select 1 from t2 where t1.c2=t2.c2);
c1 c2
1 3

正如所看到的，not in 出现了不期望的结果集，存在逻辑错误。如果看一下上述两个select语句的执行计划，也会不同。后者使用了hash_aj。
因此，请尽量不要使用not in(它会调用子查询)，而尽量使用not exists(它会调用关联子查询)。如果子查询中返回的任意一条记录含有空值，则查询将不返回任何记录，正如上面例子所示。
除非子查询字段有非空限制，这时可以使用not in ,并且也可以通过提示让它使用hasg_aj或merge_aj连接。