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 |
- 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
LastName | FirstName | OrderNo |
---|
Adams | John | 22456 |
Adams | John | 24562 |
Carter | Thomas | 77895 |
Carter | Thomas | 44678 |
left join
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
LEFT JOIN Orders
ON Persons.Id_P=Orders.Id_P
LastName | FirstName | OrderNo |
---|
Adams | John | 22456 |
Adams | John | 24562 |
Carter | Thomas | 77895 |
Carter | Thomas | 44678 |
Bush | George | |
right join
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 |
Carter | Thomas | 77895 |
Carter | Thomas | 44678 |
| | 34764 |
full join ( mysql 不支持 )
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
FULL JOIN Orders
ON Persons.Id_P=Orders.Id_P
LastName | FirstName | OrderNo |
---|
Adams | John | 22456 |
Adams | John | 24562 |
Carter | Thomas | 77895 |
Carter | Thomas | 44678 |
Bush | George | |
| | 34764 |
cross join
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
CROSS JOIN Orders
ON Persons.Id_P=Orders.Id_P
LastName | FirstName | FirstName |
---|
Adams | John | 77895 |
Bush | George | 77895 |
Carter | Thomas | 77895 |
Adams | John | 44678 |
Bush | George | 44678 |
Carter | Thomas | 44678 |
Adams | John | 22456 |
Bush | George | 22456 |
Carter | Thomas | 22456 |
Adams | John | 24562 |
Bush | George | 24562 |
Carter | Thomas | 24562 |
Adams | John | 34764 |
Bush | George | 34764 |
Carter | Thomas | 34764 |