1、连接
JOIN 是“连接”的意思,顾名思义,SQL JOIN 子句用于将两个或者多个表联合起来进行查询。
连接表时需要在每个表中选择一个字段,并对这些字段的值进行比较,值相同的两条记录将合并为一条。连接表的本质就是将不同表的记录合并起来,形成一张新表。当然,这张新表只是临时的,它仅存在于本次查询期间。
使用 JOIN
连接两个表的基本语法如下:
select table1.column1, table2.column2...
from table1
join table2
on table1.common_column1 = table2.common_column2;
table1.common_column1 = table2.common_column2
是连接条件,只有满足此条件的记录才会合并为一行。您可以使用多个运算符来连接表,例如 =、>、<、<>、<=、>=、!=、between
、like
或者 not
,但是最常见的是使用 =。
当两个表中有同名的字段时,为了帮助数据库引擎区分是哪个表的字段,在书写同名字段名时需要加上表名。当然,如果书写的字段名在两个表中是唯一的,也可以不使用以上格式,只写字段名即可。
另外,如果两张表的关联字段名相同,也可以使用 USING
子句来代替 ON
,举个例子:
# join....on
select c.cust_name, o.order_num
from Customers c
inner join Orders o
on c.cust_id = o.cust_id
order by c.cust_name;
# 如果两张表的关联字段名相同,也可以使用USING子句:join....using()
select c.cust_name, o.order_num
from Customers c
inner join Orders o
using(cust_id)
order by c.cust_name;
ON
和 WHERE
的区别:
- 连接表时,SQL 会根据连接条件生成一张新的临时表。
ON
就是连接条件,它决定临时表的生成。 WHERE
是在临时表生成以后,再对临时表中的数据进行过滤,生成最终的结果集,这个时候已经没有 JOIN-ON 了。
所以总结来说就是:SQL 先根据 ON 生成一张临时表,然后再根据 WHERE 对临时表进行筛选。
SQL 允许在 JOIN
左边加上一些修饰性的关键词,从而形成不同类型的连接,如下表所示:
连接类型 | 说明 |
---|---|
INNER JOIN 内连接 | (默认连接方式)只有当两个表都存在满足条件的记录时才会返回行。 |
LEFT JOIN / LEFT OUTER JOIN 左(外)连接 | 返回左表中的所有行,即使右表中没有满足条件的行也是如此。 |
RIGHT JOIN / RIGHT OUTER JOIN 右(外)连接 | 返回右表中的所有行,即使左表中没有满足条件的行也是如此。 |
FULL JOIN / FULL OUTER JOIN 全(外)连接 | 只要其中有一个表存在满足条件的记录,就返回行。 |
SELF JOIN | 将一个表连接到自身,就像该表是两个表一样。为了区分两个表,在 SQL 语句中需要至少重命名一个表。 |
CROSS JOIN | 交叉连接,从两个或者多个连接表中返回记录集的笛卡尔积。 |
下图展示了 LEFT JOIN、RIGHT JOIN、INNER JOIN、OUTER JOIN 相关的 7 种用法。
如果不加任何修饰词,只写 JOIN
,那么默认为 INNER JOIN
对于 INNER JOIN
来说,还有一种隐式的写法,称为 “隐式内连接”,也就是没有 INNER JOIN
关键字,使用 WHERE
语句实现内连接的功能
# 隐式内连接
select c.cust_name, o.order_num
from Customers c, Orders o
where c.cust_id = o.cust_id
order by c.cust_name;
# 显式内连接
select c.cust_name, o.order_num
from Customers c inner join Orders o
using(cust_id)
order by c.cust_name;
2、组合
UNION
运算符将两个或更多查询的结果组合起来,并生成一个结果集,其中包含来自 UNION
中参与查询的提取行。
UNION
基本规则:
- 所有查询的列数和列顺序必须相同。
- 每个查询中涉及表的列的数据类型必须相同或兼容。
- 通常返回的列名取自第一个查询。
默认地,UNION
操作符选取不同的值。如果允许重复的值,请使用 UNION ALL
。
SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;
UNION
结果集中的列名总是等于 UNION
中第一个 SELECT
语句中的列名。
JOIN
vs UNION
:
JOIN
中连接表的列可能不同,但在UNION
中,所有查询的列数和列顺序必须相同。UNION
将查询之后的行放在一起(垂直放置),但JOIN
将查询之后的列放在一起(水平放置),即它构成一个笛卡尔积。