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”中没有相匹配的数据。
sql server---join on、left join 、right join用法
最新推荐文章于 2023-09-14 19:15:55 发布