join mysql

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.OrderNoFROM PersonsINNER JOIN OrdersON 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.OrderNoFROM PersonsLEFT JOIN OrdersON 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.OrderNoFROM PersonsRIGHT JOIN OrdersON 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.OrderNoFROM PersonsFULL JOIN OrdersON 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.OrderNoFROM PersonsCROSS JOIN OrdersON 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