sql join用法学习

为了在两个或更多的表中获取结果，我们常常会用到join

inner join(又叫join）

out join包括left join,right join和full join（也就是left+right）

请看 "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

 

 

 

 

 

1、inner join：在A且在B中

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

 

 

 

 

2、left join：A的完整集，B中匹配则有值，没有则为NULL

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
Carter	Thomas	77895
Carter	Thomas	44678

 

 

 

 

 

3、right join：B的完整集，A中匹配则有值，否则为NULL

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

 

 

 

 

 

4、full join，在A或在B中

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
Bush	George
Carter	Thomas	77895
Carter	Thomas	44678
		34764