2.0join
用途:
当你要从两个或者以上的表中选取结果集时,你就会用到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”中没有相匹配的数据。