MySQL 支持的连接类型有:内连接(等值连接,INNER JOIN
),左连接(LEFT JOIN
),右连接(RIGHT JOIN
),交叉连接(CROSS JOIN
),在此基础上, SQL 标准语法中还规定另外一种 MySQL 所不支持的一种连接类型:全连接(FULL JOIN
)
通过一张图可以大致概括 SQL JOIN 语法的基本内容:
以 w3school 教程为例,首先在 MySQL数据库中分别建立如下两张用于测试的表:
Persons 表:
Id_P | LastName | FirstName | Address | City |
---|---|---|---|---|
1 | Adams | John | Oxford Str | London |
2 | Bush | George | Fifth Aven | New York |
3 | Carter | Thomas | Changan St | Beijing |
Orders 表:
Id_O | OrderNo | Id_P |
---|---|---|
1 | 77895 | 3 |
2 | 44678 | 3 |
3 | 22456 | 1 |
4 | 24562 | 1 |
5 | 34764 | 65 |
1.内连接:INNER JOIN
INNER JOIN 也可以简写成 JOIN ,配合 ON 条件使用时,表示取得两张表中同时符合 ON 条件的结果集(取交集)。
例如:
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
INNER JOIN Orders
ON Persons.Id_P = Orders.Id_P
ORDER BY Persons.LastName;
结果:
LastName | FirstName | OrderNo |
---|---|---|
Adams | John | 24562 |
Adams | John | 22456 |
Carter | Thomas | 77895 |
Carter | Thomas | 44678 |
2.左连接:LEFT JOIN
取得左表中所有行以及右表中符合 ON 条件的行
例如:
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
LEFT JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName;
结果:
LastName | FirstName | OrderNo |
---|---|---|
Adams | John | 22456 |
Adams | John | 24562 |
Bush | George | NULL |
Carter | Thomas | 77895 |
Carter | Thomas | 44678 |
3.右连接:RIGHT JOIN
取得右表中的所有行以及左表中符合 ON 条件的行
例如:
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
RIGHT JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName;
结果:
LastName | FirstName | OrderNo |
---|---|---|
NULL | NULL | 34764 |
Adams | John | 24562 |
Adams | John | 22456 |
Carter | Thomas | 77895 |
Carter | Thomas | 44678 |
4.交叉连接:CROSS JOIN
对于不使用 ON 条件的交叉连接,其结果是取得了两张数据表的笛卡尔乘积,即获取两张表中数据行任意可能的组合结果。
例如:
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
CROSS JOIN Orders
ORDER BY Persons.LastName;
结果:
LastName | FirstName | OrderNo |
---|---|---|
Adams | John | 44678 |
Adams | John | 34764 |
Adams | John | 22456 |
Adams | John | 77895 |
Adams | John | 24562 |
Bush | George | 44678 |
Bush | George | 34764 |
Bush | George | 22456 |
Bush | George | 77895 |
Bush | George | 24562 |
Carter | Thomas | 24562 |
Carter | Thomas | 44678 |
Carter | Thomas | 34764 |
Carter | Thomas | 22456 |
Carter | Thomas | 77895 |
值得注意的一点是,在 MySQL 中(仅限于 MySQL),在不指定 ON 条件时候, INNER JOIN、CROSS JOIN、JOIN 取得的效果是一样的!因此如果想获取真正的交集,一定要指定相应的 ON 条件!
5.全连接:FULL JOIN
(MySQL 不支持)
SQL 语法中将 FULL JOIN 定义为获取两张表中所有行以及符合 ON 条件的行,类似于取并集。
MySQL 虽然不能直接使用 FULL JOIN,但是通过 UNION
来组合 LEFT JOIN 以及 RIGHT JOIN 的结果集可以类似地实现同样的效果,例如:
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons
LEFT JOIN Orders
ON Persons.Id_P=Orders.Id_P
UNION
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons
RIGHT JOIN Orders
ON Persons.Id_P=Orders.Id_P;
结果:
LastName | FirstName | OrderNo |
---|---|---|
Adams | John | 22456 |
Adams | John | 24562 |
Bush | George | NULL |
Carter | Thomas | 77895 |
Carter | Thomas | 44678 |
NULL | NULL | 34764 |