-----内联结3个表返回产品的折扣信息-----
SELECT p.Name,s.DiscountPct from Sales.SpecialOffer s
inner JOIN Sales.SpecialOfferProduct o ON
s.SpecialOfferID=o.SpecialOfferID
INNER JOIN Production.Product p ON
o.ProductID=p.ProductID
WHERE p.Name ='All-Purpose Bike Stand'
-----外联结2个表返回税率状态、省-----
SELECT s.CountryRegionCode,s.StateProvinceCode,t.TaxType,t.TaxRate FROM Person.StateProvince s
LEFT OUTER JOIN Sales.SalesTaxRate t ON
s.StateProvinceID=t.StateProvinceID
-----比较内联结-----
SELECT s.CountryRegionCode,s.StateProvinceCode,t.TaxType,t.TaxRate FROM Person.StateProvince s
INNER JOIN Sales.SalesTaxRate t ON
s.StateProvinceID=t.StateProvinceID
-----交叉联结-----
SELECT s.CountryRegionCode,s.StateProvinceCode,t.TaxType,t.TaxRate FROM Person.StateProvince s
CROSS JOIN Sales.SalesTaxRate t
-----实现自联结-----
SELECT e.EmployeeID,e.Title,m.Title AS ManagerTitle
from HumanResources.Employee e
LEFT OUTER JOIN HumanResources.Employee m ON
e.ManagerID = m.EmployeeID
-----使用衍生表------
SELECT DISTINCT s.PurchaseOrderNumber
FROM Sales.SalesOrderHeader s
INNER JOIN (SELECT SalesOrderID
FROM Sales.SalesOrderDetail
WHERE UnitPrice BETWEEN 1000 AND 2000) d ON
s.SalesOrderID=d.SalesOrderID
-----使用 UNION 组合结果集-----
SELECT SalesPersonID,GETDATE() QuotaDate,SalesQuota
FROM Sales.SalesPerson
WHERE SalesQuota>0
UNION
SELECT SalesPersonID,QuotaDate,SalesQuota
FROM Sales.SalesPersonQuotaHistory
WHERE SalesQuota>0
ORDER BY SalesPersonID DESC,QuotaDate DESC
