SQL Server: JOIN

结果集：INNER JOIN < OUTER JOIN < FULL JOIN < CROSS JOIN

INNER JOIN: 如果任意一行在两个表中不匹配，则注定将从最终的结果集中排除掉。

几种写法

一、

SELECT *

FROM HumanResources.Employee e

INNER JOIN HumanResources.Employee e

ON e.ManagerID = m.EmployeeID

二、

SELECT *

FROM HumanResources.Employee e

JOIN HumanResources.Employee e

ON e.ManagerID = m.EmployeeID

三、

SELECT *

FROM HumanResources.Employee e ,HumanResources.Employee m

WHERE e.ManagerID = m.EmployeeID

(注意：如果这样

SELECT *

FROM HumanResources.Employee e ,HumanResources.Employee m

将是 CROSS JOIN)

 

OUTER JOIN :  LEFT OUTER JOIN and RIGHT OUTER JOIN, 第一个被指定的表认为是在左侧，第二个被指定的表是在右侧, LEFT OUTER JOIN 选出左边表中所有的数据，不管是否与右边表中有匹配

FROM HumanResources.Employee e

LEFT OUTER JOIN HumanResources.Employee e

ON e.ManagerID = m.EmployeeID

FULL JOIN: 把LEFT 和RIGHT连接结合到一起

CROSS JOIN: 笛卡尔积