内联接

--
--2.3.1节示例
--

--内联接
SELECT a.City,sp.Name
FROM Person.Address a
INNER JOIN Person.StateProvince sp  --内联接
ON sp.StateProvinceID = a.StateProvinceID

--使用WHERE子句的内联接
SELECT a.City,sp.Name
FROM Person.Address a,Person.StateProvince sp
WHERE sp.StateProvinceID = a.StateProvinceID
--等同于INNER JOIN，但不推荐使用这样形式

--多个表的内联接
SELECT a.City,sp.Name,cr.Name
FROM Person.Address a
INNER JOIN Person.StateProvince sp --a表与sp表进行内联接
ON sp.StateProvinceID = a.StateProvinceID
INNER JOIN Person.CountryRegion cr --同时再与cr表做内联接
ON cr.CountryRegionCode = sp.CountryRegionCode

--
--2.3.2节示例
--

--左联接
SELECT m.LoginID AS ManagerLoginID,e.*
FROM HumanResources.Employee e
LEFT JOIN HumanResources.Employee m --左联接
ON m.EmployeeID = e.ManagerID

--右联接
SELECT m.LoginID AS ManagerLoginID,e.*
FROM HumanResources.Employee m
RIGHT JOIN HumanResources.Employee e --右联接
ON m.EmployeeID = e.ManagerID

--外联接查找不匹配记录
SELECT s.*
FROM Student s
LEFT JOIN Class c
ON s.ClassID=c.ClassID
WHERE c.ClassID IS NULL --为匹配班号对应的Class必定为NULL

--
--2.3.3节示例
--

--完全联接
SELECT m.LoginID AS ManagerLoginID,e.*
FROM HumanResources.Employee m
FULL JOIN HumanResources.Employee e --m表与e表完全联接
ON m.EmployeeID = e.ManagerID

--
--2.3.4节示例
--

--交叉联接
SELECT sp.StateProvinceID,sp.Name ,at.AddressTypeID,at.Name AS AddressTypeName
FROM Person.StateProvince sp
CROSS JOIN person.AddressType at --交叉联接

--
--2.3.5节示例
--

--CROSS JION的替代写法
SELECT sp.StateProvinceID,sp.Name ,
at.AddressTypeID,at.Name AS AddressTypeName
FROM Person.StateProvince sp , person.AddressType at

--
--2.3.6节示例
--

--UNION的使用
SELECT at.AddressTypeID,at.Name,at.ModifiedDate
FROM Person.AddressType at
UNION
SELECT ct.ContactTypeID,ct.Name,ct.ModifiedDate
FROM Person.ContactType ct

--使用UNION ALL
SELECT at.AddressTypeID,at.Name,at.ModifiedDate
FROM Person.AddressType at
UNION ALL
SELECT ct.ContactTypeID,ct.Name,ct.ModifiedDate
FROM Person.ContactType ct