Join
用途：
当你要从两个或者以上的表中选取结果集时，你就会用到JOIN。
例：
“Employees”表中的数据如下，（其中ID为主键）:
|
ID |
Name |
|
01 |
Hansen, Ola |
|
02 |
Svendson, Tove |
|
03 |
Svendson, Stephen |
|
04 |
Pettersen, Kari |
“Orders”表中的数据如下：
|
ID |
Product |
|
01 |
Printer |
|
03 |
Table |
|
03 |
Chair |
用Employees的ID和Orders的ID相关联选取数据：
SELECT Employees.Name, Orders.Product FROM Employees, Orders WHERE Employees.ID = Orders.ID |
返回结果：
|
Name |
Product |
|
Hansen, Ola |
Printer |
|
Svendson, Stephen |
Table |
|
Svendson, Stephen |
Chair |
或者你也可以用JOIN关键字来完成上面的操作：
SELECT Employees.Name, Orders.Product FROM Employees INNER JOIN Orders ON Employees.ID = Orders.ID |
INNER JOIN的语法：
SELECT field1, field2, field3 FROM first_table INNER JOIN second_table ON first_table.keyfield = second_table.foreign_keyfield |
解释：
INNER JOIN返回的结果集是两个表中所有相匹配的数据。
LEFT JOIN的语法：
SELECT field1, field2, field3 FROM first_table LEFT JOIN second_table ON first_table.keyfield = second_table.foreign_keyfield |
用”Employees”表去左外联结”Orders”表去找出相关数据：
SELECT Employees.Name, Orders.Product FROM Employees LEFT JOIN Orders ON Employees.ID = Orders.ID |
返回结果：
|
Name |
Product |
|
Hansen, Ola |
Printer |
|
Svendson, Tove |
|
|
Svendson, Stephen |
Table |
|
Svendson, Stephen |
Chair |
|
Pettersen, Kari |
|
解释：
LEFT JOIN返回”first_table”中所有的行尽管在” second_table”中没有相匹配的数据。
RIGHT JOIN的语法：
SELECT field1, field2, field3 FROM first_table RIGHT JOIN second_table ON first_table.keyfield = second_table.foreign_keyfield |
用”Employees”表去右外联结”Orders”表去找出相关数据：
SELECT Employees.Name, Orders.Product FROM Employees RIGHT JOIN Orders ON Employees.ID = Orders.ID |
返回结果：
|
Name |
Product |
|
Hansen, Ola |
Printer |
|
Svendson, Stephen |
Table |
|
Svendson, Stephen |
Chair |
解释：
RIGHT JOIN返回” second_table”中所有的行尽管在”first_table”中没有相匹配的数据。
|
