MySQL联结查询

2022-09-01 21:50:50

MySQL联结查询

操作系统：ubuntu 18.04
mysql版本：8.0
创建：2020/7/2
修改：2020/7/2

使用的演示表：

用户表(users)

订单表(orders)

联结的种类：内联结，左外联结，右外联结，全外联结，自联结，自然联结

内联结(inner join)

内联结也被称为等值联结，是将两张或多张表通过联结列进行匹配联结为一张表。以联结两个表为例，指定一列或多列作为联结列，使用第一张表中一行中的指定列与第二张表中每一行对应的列比较，如果这些列都与对应列相等，那么将这两行拼接加入联结表。在第二张表中找不到匹配的行不会出现在联结表中。联结表行数可能会超过第一张表，因为在第二张表中可能有多行与第一张表中的一行匹配。

内联结有两种写法，两种写法结果一样，使用join的写法更加标准

-- 内联结不使用JOIN
SELECT *
FROM users, orders
WHERE users.userId = orders.userId;

-- 内联结使用JOIN
SELECT *
FROM users JOIN orders 
ON users.userId = orders.userId;

当不使用联结条件时，返回的结果是笛卡尔积，也就是第一张表中的每一行和第二张表中所有行匹配，最终结果的行数为第一张表行数乘以第二张表的行数

SELECT *
FROM users JOIN orders;

在使用多个列联结或者联结多张表时，可以使用AND拼接条件

SELECT *
FROM users JOIN orders JOIN order_items
ON users.userId = orders.userId AND orders.orderId = order_items.orderId;

在MySQL中还可以进行简写

SELECT *
FROM users JOIN orders USING(userId);

简写的条件是在两张表中联结列的列名是相同的，使用USING后结果集中会去除重复的联结列

外联结(outer join)

外联结与内联结相似，区别是外联结将在另一个表中没有匹配的行与全是null值的行匹配
外联结分为：左外连接，右外联结，全外联结

左外联结(left outer join)
以左边表做主表，左边的表中不能与右边表匹配的行，与全是null值的行匹配并加入联结表。
这样左边表中的所有行都在联结表中至少出现了一次

-- 使用ON
SELECT *
FROM users LEFT OUTER JOIN orders
ON users.userId = orders.userId;

-- 使用USING
SELECT *
FROM users LEFT OUTER JOIN orders USING(userId);

右外联结(right outer join)
右外联结以右边的表作为主表，右边表中不能在左边表匹配的行，与全是null值的行匹配。
这样右边表中所有的行都在联结表中至少出现了一次

-- 使用ON
SELECT *
FROM users RIGHT OUTER JOIN orders
ON users.userId = orders.userId;

-- 使用USING
SELECT *
FROM users RIGHT OUTER JOIN orders USING(userId);

左外联结和右外联结区别仅在顺序，比如下面两句效果相同

A LEFT OUTER JOIN B;
B RIGHT OUTER JOIN A;

全外联结(full outer join)
全外联结是将左右两张表中，不能与另一张表中行匹配的行都与全是null值的行匹配，MySQL不支持FULL OUTER JOIN，可以使用UNION实现，UNION会去除那些重复的行

-- 这里不能使用USING，使用USING会使两个结果集中列的对应关系错乱
SELECT *
FROM users LEFT OUTER JOIN orders
ON users.userId = orders.userId
UNION
SELECT *
FROM users RIGHT OUTER JOIN orders
ON users.userId = orders.orderId;

自然联结(natural join)

自然联结是一种特殊的等值联结。在联结两个表时，使用两个表的公共列进行联结，公共列是指列名和值类型都相同的列，并且在结果中会去除冗余的联结列。

-- 使用NATURAL JOIN进行自然联结
SELECT *
FROM users NATURAL JOIN orders;

-- 上面语句的结果，与此句使用内联结的结果相同
SELECT *
FROM users JOIN orders USING(userId);

上面的例子因为users和orders表中都有userId这个列，并且值类型也相同，所以使用userId进行联结。在自然联结中不能使用ON或 USING来限制

自联结(self join)

自联结就是一张表和自己联结，比如要找出users表中和伞木希美同一个年龄的用户，我们可以使用子查询先得到伞木希美的年龄，再查询这个年龄的所有用户，也可以使用自联结完成此项工作

-- 使用子查询
SELECT *
FROM users
WHERE age = (
	SELECT age
	FROM users
	WHERE name = ‘伞木希美‘
	);
	
-- 使用自联结
SELECT u.*
FROM users JOIN users AS u USING(age)
WHERE users.name = ‘伞木希美‘;

MySQL联结查询

码农公寓