SQL使用——联合查询
1.联合查询分类
内连接(inner Join 或 Join)
外连接(outer Join)
左外连接(left outer Join 或 left Join)
右外连接(right outer Join 或 right Join)
全外连接(full outer Join 或 full Join)
交叉连接 (cross Join)
结果集链接 (union 和 union all)
2.联合查询介绍
A
AB
C
2.1内连接(Inner Join)
两表查询
SELECT A.id AS AID,
A.content AS AContent,
B.id AS BID,
B.content AS BContentFROM AINNER JOIN B ON (A.id = B.id)
结果:
多表查询
SELECT
A.id AS AID,
A.content AS AContent,
B.id AS BID,
B.content AS BContent,
C.id AS CID,
C.content AS CContent
FROM
A
INNER JOIN B ON (A.id = B.id)
INNER JOIN C ON (A.id = C.id)
结果:
2.1外连接
1,左连接。显示左表所有部分。且可以显示右表的字段。
SELECT
A.id AS AID,
A.content AS AContent,
B.id AS BID,
B.content AS BContent
FROM
A
LEFT JOIN B ON (A.id = B.id);
结果:
外链接升级:
select
a.*
left join b on a.name =b.Name
where
b.Name is null
原理,字段AB中都有,是AB的交集,is null为条件,说明,B的字段在左连接查询后的条件中不存在,那就把交集去掉了。
2,右连接。显示右表所有部分。且可以显示左表的字段。
SELECT
A.id AS AID,
A.content AS AContent,
B.id AS BID,
B.content AS BContent
FROM
A
RIGHT JOIN B ON (A.id = B.id);
外链接升级:同上!
3,满链接
可使用左链接联合有链接
select
*
from
A left join B on A.id=B.id
union
select
*
from
A right join B on A.id=B.id
(where A.id is null )
这里,union可以去重,虽然交集搜索了两次,但是结果集只会显示一次。如果使用 union all ,则会不去重,所有显示
当两个表结构相同时
select * from A UNION select * from B UNION 会将AB相同结果合并 union all则不会。
升级思考:这种场景呢?两个表交集之外的所有!
select
*
from
A left join B on A.id=B.id
where B.id is null
union
select
*
from
A right join B on A.id=B.id
where A.id is null
网友评论