join mysql

最新推荐文章于 2025-01-21 16:35:15 发布

原创最新推荐文章于 2025-01-21 16:35:15 发布 · 328 阅读

CC 4.0 BY-SA版权

1 篇文章

订阅专栏

本文详细介绍了SQL中的不同连接类型：INNER JOIN、LEFT JOIN、RIGHT JOIN 和 FULL JOIN 的使用方法及应用场景，并通过具体示例展示了每种连接方式如何帮助开发者获取所需的数据记录。

Persons

Id_P	LastName	FirstName	Address	City
1	Adams	John	Oxford Street	London
2	Bush	George	Fifth Avenue	New York
3	Carter	Thomas	Changan Street	Beijing

Orders

- JOIN: 如果表中有至少一个匹配，则返回行
- LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行
- RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的行
- FULL JOIN: 只要其中一个表中存在匹配，就返回行

join 等同于 inner join

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
INNER JOIN Orders
ON Persons.Id_P = Orders.Id_P

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
LEFT JOIN Orders
ON Persons.Id_P=Orders.Id_P

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
RIGHT JOIN Orders
ON Persons.Id_P=Orders.Id_P

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
FULL JOIN Orders
ON Persons.Id_P=Orders.Id_P

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
CROSS JOIN Orders
ON Persons.Id_P=Orders.Id_P