多表查询
Person表:
Address表:
内连接
查询两表交集部分数据。
#显式内连接
SELECT p.PersonId, p.FirstName, p.LastName, a.City, a.State
FROM Person p INNER JOIN Address a
ON p.PersonId = a.PersonId;
#隐式内连接
SELECT p.PersonId, p.FirstName, p.LastName, a.City, a.State
FROM Person p, Address a
WHERE p.PersonId = a.PersonId;
查询结果:
左外连接
查询左表所有数据,以及两表交集部分数据。
#左外连接
SELECT p.PersonId, p.FirstName, p.LastName, a.City, a.State
FROM Person p LEFT OUTER JOIN Address a
ON p.PersonId = a.PersonId;
查询结果:
右外连接
查询右表所有数据,以及两表交集部分数据。
SELECT p.PersonId, p.FirstName, p.LastName, a.City, a.State
FROM Person p RIGHT OUTER JOIN Address a
ON p.PersonId = a.PersonId;
查询结果:
查询只在左表不在右表的数据
SELECT p.PersonId, p.FirstName, p.LastName, a.City, a.State
FROM Person p LEFT OUTER JOIN Address a
ON p.PersonId = a.PersonId
WHERE a.AddressId IS NULL;
查询结果:
查询只在右表不在左表的数据
SELECT p.PersonId, p.FirstName, p.LastName, a.City, a.State
FROM Person p RIGHT OUTER JOIN Address a
ON p.PersonId = a.PersonId
WHERE p.PersonId IS NULL;
查询结果:
联合查询
利用union联合查询,把多次查询的结果合并起来,形成一个新的查询结果。
排除左右表公共部分数据
要排除左右表公共部分数据,即可用union联合“只在左表不在右表的数据”和“只在右表不在左表的数据”的查询结果。
SELECT p.PersonId, p.FirstName, p.LastName, a.City, a.State
FROM Person p RIGHT OUTER JOIN Address a
ON p.PersonId = a.PersonId
WHERE p.PersonId IS NULL
UNION
SELECT p.PersonId, p.FirstName, p.LastName, a.City, a.State
FROM Person p LEFT OUTER JOIN Address a
ON p.PersonId = a.PersonId
WHERE a.AddressId IS NULL;
查询结果:
全外连接:查询左右表所有数据
要排除左右表公共部分数据,即可用union联合“只在左表不在右表的数据”和“右外连接的数据”的查询结果。
#查询左右表所有数据
SELECT p.PersonId, p.FirstName, p.LastName, a.City, a.State
FROM Person p LEFT OUTER JOIN Address a
ON p.PersonId = a.PersonId
WHERE a.AddressId IS NULL
UNION
SELECT p.PersonId, p.FirstName, p.LastName, a.City, a.State
FROM Person p RIGHT OUTER JOIN Address a
ON p.PersonId = a.PersonId;
查询结果:
或者用union联合“左外连接的数据”和“只在右表不在左表的数据”的查询结果。
SELECT p.PersonId, p.FirstName, p.LastName, a.City, a.State
FROM Person p LEFT OUTER JOIN Address a
ON p.PersonId = a.PersonId
UNION
SELECT p.PersonId, p.FirstName, p.LastName, a.City, a.State
FROM Person p RIGHT OUTER JOIN Address a
ON p.PersonId = a.PersonId
WHERE p.PersonId IS NULL;
查询结果一样。