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 PersonsINNER 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 PersonsLEFT 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 PersonsRIGHT 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 PersonsFULL 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 |