SQL技术：sql语句子查询大全

<!-- /* Font Definitions */ @font-face {font-family:宋体; panose-1:2 1 6 0 3 1 1 1 1 1; mso-font-alt:SimSun; mso-font-charset:134; mso-generic-font-family:auto; mso-font-pitch:variable; mso-font-signature:3 135135232 16 0 262145 0;} @font-face {font-family:"/@宋体"; panose-1:2 1 6 0 3 1 1 1 1 1; mso-font-charset:134; mso-generic-font-family:auto; mso-font-pitch:variable; mso-font-signature:3 135135232 16 0 262145 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0cm; margin-bottom:.0001pt; text-align:justify; text-justify:inter-ideograph; mso-pagination:none; font-size:10.5pt; mso-bidi-font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:宋体; mso-font-kerning:1.0pt;} /* Page Definitions */ @page {mso-page-border-surround-header:no; mso-page-border-surround-footer:no;} @page Section1 {size:612.0pt 792.0pt; margin:72.0pt 90.0pt 72.0pt 90.0pt; mso-header-margin:36.0pt; mso-footer-margin:36.0pt; mso-paper-source:0;} div.Section1 {page:Section1;} -->

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 连接。