join、union、关联查询

1、join、union、关联查询

• 内连接查询 (select * from a join b on a.id = b.id)
• 关联查询 (select * from a , b where a.id = b.id)
• UNION 操作符用于合并两个或多个 SELECT 语句的结果集

2、on、where、having的执行顺序以及效率

from a join b 与 from a, b 产生的临时表结果集 都是执行笛卡尔积即 (select * from a cross join b )两表的行乘积数。

• on：与取得结果集，同步进行数据筛选及过滤。

• where：获得结果集之后，才进行数据筛选及过滤。

执行顺序：on在上游，where在中游，having在下游。

3、left join、right join、inner join、full join

sql中的连接查询有inner join(内连接）、left join(左连接)、right join（右连接）、full join（全连接）四种方式，它们之间其实并没有太大区别，仅仅是查询出来的结果有所不同。

例如有两张表：

Orders表通过外键Id_P和Persons表进行关联。

3.1 inner join（内连接）

在两张表进行连接查询时，只保留两张表中完全匹配的结果集

SELECT p.LastName, p.FirstName, o.OrderNo
FROM Persons p
INNER JOIN Orders o
ON p.Id_P=o.Id_P and 1=1　　--用and连接多个条件
ORDER BY p.LastName

查询结果集：

注意：单纯的select * from a,b是笛卡尔乘积。比如a表有5条数据，b表有3条数据，那么最后的结果有5*3=15条数据。

另：

• 对两个表进行关联：select * from a,b where a.id = b.id
• 此时就等价于内连接：select * from a inner join b on a.id = b.id

但是这种写法并不符合规范，可能只对某些数据库管用，如sqlserver。推荐最好不要这样写。最好写成inner join的写法。

3.2 left join

在两张表进行连接查询时，会返回左表所有的行，即使在右表中没有匹配的记录。

SELECT p.LastName, p.FirstName, o.OrderNo
FROM Persons p
LEFT JOIN Orders o
ON p.Id_P=o.Id_P
ORDER BY p.LastName

3.3 right join

在两张表进行连接查询时，会返回右表所有的行，即使在左表中没有匹配的记录。

SELECT p.LastName, p.FirstName, o.OrderNo
FROM Persons p
RIGHT JOIN Orders o
ON p.Id_P=o.Id_P
ORDER BY p.LastName

3.4 full join

在两张表进行连接查询时，返回左表和右表中所有没有匹配的行。

SELECT p.LastName, p.FirstName, o.OrderNo
FROM Persons p
FULL JOIN Orders o
ON p.Id_P=o.Id_P
ORDER BY p.LastName

4、UNION

UNION 操作符用于合并两个或多个 SELECT 语句的结果集，请注意UNION 内部的 SELECT 语句必须：

• 拥有相同数量的列
• 列必须拥有相似的数据类型
• 每条 SELECT 语句中的列的顺序必须相同

默认地：

• UNION 默认操作符选取不同的值
• 如果允许重复的值，请使用 UNION ALL

4.1 UNION

列出所有在中国和美国的不同的雇员名：

SELECT E_Name FROM Employees_China
UNION
SELECT E_Name FROM Employees_USA

结果：

这个命令无法列出在中国和美国的所有雇员。在上面的例子中，我们有两个名字相同的雇员，他们当中只有一个人被列出来了。UNION 命令只会选取不同的值。

4.2 UNION ALL

UNION ALL 命令和 UNION 命令几乎是等效的，不过 UNION ALL 命令会列出所有的值。

列出在中国和美国的所有的雇员：

SELECT E_Name FROM Employees_China
UNION ALL
SELECT E_Name FROM Employees_USA

结果：

从效率上说，UNION ALL 要比UNION快很多，所以，如果可以确认合并的两个结果集中不包含重复数据且不需要排序时的话，那么就使用UNION ALL。