数据库查询join的知识总结

最新推荐文章于 2023-02-19 15:18:21 发布

原创最新推荐文章于 2023-02-19 15:18:21 发布 · 684 阅读

1 ·

本内容遵循CC 4.0 BY-SA版权协议

数据库专栏收录该内容

21 篇文章

订阅专栏

本文详细介绍了SQL中的JOIN操作，包括INNER JOIN（等值连接）、LEFT JOIN（左连接）、RIGHT JOIN（右连接）和FULL JOIN（全连接）。通过实例展示了不同JOIN类型如何在不同情况下返回数据，帮助理解如何根据需求选择合适的JOIN类型。

在sql当中，有下面这些join类型：

（INNER）JOIN：返回两个表中具有匹配值的记录
LEFT（OUTER）JOIN：返回左侧表中的所有记录，以及右侧表中的匹配记录
RIGHT (OUTER) JOIN：从右表返回所有记录，并从左表返回匹配的记录
FULL（OUTER）JOIN：当左表或右表中存在匹配项时，返回所有记录

分别如下图所示：

Inner join

INNER JOIN和JOIN是一个意思。

INNER JOIN选择两个表中具有匹配值的记录：

一个INNER JOIN的例子如下：

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

示例database如下：

以下是Orders表中的一些数据：

OrderID	CustomerID	EmployeeID	OrderDate	ShipperID
10308	2	7	1996-09-18	3
10309	37	3	1996-09-19	1
10310	77	8	1996-09-20	2

下面是Customers表中的一些数据：

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
1	Alfreds Futterkiste	Maria Anders	Obere Str. 57	Berlin	12209	Germany
2	Ana Trujillo Emparedados y helados	Ana Trujillo	Avda. de la Constitución 2222	México D.F.	05021	Mexico
3	Antonio Moreno Taquería	Antonio Moreno	Mataderos 2312	México D.F.	05023	Mexico

以下SQL语句选择所有包含客户信息的订单：

SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

该查询的查询结果为

OrderID	CustomerName
10308	Ana Trujillo Emparedados y helados

再来看另一个例子，假设我们现在有如下的database：

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表如下：

Id_O	OrderNo	Id_P
1	77895	3
2	44678	3
3	22456	1
4	24562	1
5	34764	65

我们希望查出所有人的订单，使用inner join查询如下：

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	22456
Adams	John	24562
Carter	Thomas	77895
Carter	Thomas	44678

INNER JOIN 关键字在表中存在至少一个匹配时返回行。如果 "Persons" 中的行在 "Orders" 中没有匹配，就不会列出这些行。

LEFT JOIN

LEFT JOIN关键字从左表（表1）返回所有记录，并从右表（表2）返回匹配的记录。如果不匹配，则右侧结果为NULL。

示例database如下：

下面是Customers表中的一些数据：

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
1	Alfreds Futterkiste	Maria Anders	Obere Str. 57	Berlin	12209	Germany
2	Ana Trujillo Emparedados y helados	Ana Trujillo	Avda. de la Constitución 2222	México D.F.	05021	Mexico
3	Antonio Moreno Taquería	Antonio Moreno	Mataderos 2312	México D.F.	05023	Mexico

下面是Orders表中的数据：

OrderID	CustomerID	EmployeeID	OrderDate	ShipperID
10308	2	7	1996-09-18	3
10309	37	3	1996-09-19	1
10310	77	8	1996-09-20	2

下面是一个LEFT JOIN的例子：

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID

上面的查询结果为：

CustomerName	OrderID
Alfreds Futterkiste	NULL
Ana Trujillo Emparedados y helados	10308
Antonio Moreno Taquería	NULL

LEFT JOIN 关键字会从左表 (Customers) 那里返回所有的行，即使在右表 (Orders) 中没有匹配的行。

RIGHT JOIN

在某些数据库中， RIGHT JOIN 称为 RIGHT OUTER JOIN， RIGHT JOIN的写法如下：

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

示例database如下：

下面是orders表中的一些数据：

OrderID	CustomerID	EmployeeID	OrderDate	ShipperID
10308	2	7	1996-09-18	3
10309	37	3	1996-09-19	1
10310	77	8	1996-09-20	2

下面是Employees表中的一些数据：

EmployeeID	LastName	FirstName	BirthDate	Photo
1	Davolio	Nancy	12/8/1968	EmpID1.pic
2	Fuller	Andrew	2/19/1952	EmpID2.pic
3	Leverling	Janet	8/30/1963	EmpID3.pic

一个right join的例子为：

SELECT Orders.OrderID, Employees.LastName, Employees.FirstName
FROM Orders
RIGHT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID

上面查询的查询结果为：

OrderID	LastName	FirstName
NULL	Davolio	Nancy
NULL	Fuller	Andrew
10310	Leverling	Janet

RIGHT JOIN 关键字会从右表 (Employees) 那里返回所有的行，即使在左表 (orders) 中没有匹配的行

FULL JOIN

full join的语法如下：

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name
WHERE condition;

示例database如下：

下面是Customers表中的一些数据：

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
1	Alfreds Futterkiste	Maria Anders	Obere Str. 57	Berlin	12209	Germany
2	Ana Trujillo Emparedados y helados	Ana Trujillo	Avda. de la Constitución 2222	México D.F.	05021	Mexico
3	Antonio Moreno Taquería	Antonio Moreno	Mataderos 2312	México D.F.	05023	Mexico

下面是Orders表中的一些数据：

OrderID	CustomerID	EmployeeID	OrderDate	ShipperID
10308	2	7	1996-09-18	3
10309	37	3	1996-09-19	1
10310	77	8	1996-09-20	2

使用full join查询的sql如下：

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders ON Customers.CustomerID=Orders.CustomerID

其查询结果为：

CustomerName	OrderID
Alfreds Futterkiste	NULL
Ana Trujillo Emparedados y helados	10308
Antonio Moreno Taquería	NULL
NULL	10309
NULL	10310

再来看另一个例子

Person表如下：

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表如下：

Id_O	OrderNo	Id_P
1	77895	3
2	44678	3
3	22456	1
4	24562	1
5	34764	65

现有full join如下：

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

则其查询结果为：

LastName	FirstName	OrderNo
Adams	John	22456
Adams	John	24562
Carter	Thomas	77895
Carter	Thomas	44678
Bush	George
		34764

FULL JOIN 关键字会从左表 (Persons) 和右表 (Orders) 那里返回所有的行。如果 "Persons" 中的行在表 "Orders" 中没有匹配，或者如果 "Orders" 中的行在表 "Persons" 中没有匹配，这些行同样会列出。