T-SQL學習中–內聯接，外連線，交叉連線

樊瀟潔發表於2015-05-02
SQL

交叉連線可以表A和表B是同一張表取得笛卡爾乘積。
比如說下面這種寫法：

SQLSELECT D.n AS theday, S.n AS shiftno  
FROM dbo.Nums AS D
  cross JOIN dbo.Nums AS S
WHERE D.n <= 7
  AND S.N <= 3
ORDER BY theday, shiftno;

當然也可以表A和表B是兩張不同的表，取得笛卡爾乘積。

SQLSELECT D.n AS theday, S.empid AS shiftno  
FROM dbo.Nums AS D
  cross JOIN [HR].[Employees] AS S
WHERE D.n <= 7
  AND S.empid <= 3
ORDER BY theday, shiftno;

但是CROSS JOIN不能用ON條件，只能用WHERE條件。下面這句與上面的語句查詢結果相同。

SQLSELECT D.n AS theday, S.empid AS shiftno  
FROM dbo.Nums AS D
  inner JOIN [HR].[Employees] AS S
on D.n <= 7
  AND S.empid <= 3
ORDER BY theday, shiftno;

內聯接查詢，表A和表B中的資料必須緊密對應，不可以是Null。下面的查詢中，Production.Products表中沒有商品記錄的的日本供貨商不會被列出來。INNER這個關鍵詞是可以捨去的，如果只寫JOIN就表示INNER JOIN。

SQLSELECT
  S.companyname AS supplier, S.country,
  P.productid, P.productname, P.unitprice
FROM Production.Suppliers AS S
  INNER JOIN Production.Products AS P
    ON S.supplierid = P.supplierid
WHERE S.country = N`Japan`;

外連線查詢有三種情況：左外連線，右外連線，全外連線。
下面這個查詢與上面這個查詢寫法只差一點點（WHERE變成了AND），但是結果就有區別：

SQLSELECT
  S.companyname AS supplier, S.country,
  P.productid, P.productname, P.unitprice
FROM Production.Suppliers AS S
  INNER JOIN Production.Products AS P
    ON S.supplierid = P.supplierid
    AND S.country = N`Japan`;

而且Production.Products表中沒有商品記錄的的日本供貨商同樣也會被列出來，但是相關的P.productid, P.productname, P.unitprice都會被記為NULL。
下面這句：

SQLSELECT E.empid,
  E.firstname + N` ` + E.lastname AS emp,
  M.firstname + N` ` + M.lastname AS mgr
FROM HR.Employees AS E
  INNER JOIN HR.Employees AS M
    ON E.mgrid = M.empid;

用了內聯接，則最高主管（CEO）不會被列出來，因為最高主管沒有更高的主管了。
而改用左外連線

SQLSELECT E.empid,
  E.firstname + N` ` + E.lastname AS emp,
  M.firstname + N` ` + M.lastname AS mgr
FROM HR.Employees AS E
  LEFT OUTER JOIN HR.Employees AS M
    ON E.mgrid = M.empid;

則CEO也會被列出來，CEO對應的mgr會被記為NULL。
套用內聯接的左外連線：

SQLSELECT
  S.companyname AS supplier, S.country,
  P.productid, P.productname, P.unitprice,
  C.categoryname
FROM Production.Suppliers AS S
  LEFT OUTER JOIN Production.Products AS P
    ON S.supplierid = P.supplierid
  INNER JOIN Production.Categories AS C
    ON C.categoryid = P.categoryid
WHERE S.country = N`Japan`;

查詢出日本供貨商的所有的產品以及產品類別名。而且Production.Products表中沒有商品記錄的的日本供貨商同樣也會被列出來，但是相關的P.productid, P.productname, P.unitprice, C.categoryname都會被記為NULL。
上面的語句與下面帶括號的語句等同：

SQLSELECT
  S.companyname AS supplier, S.country,
  P.productid, P.productname, P.unitprice,
  C.categoryname
FROM Production.Suppliers AS S
  LEFT OUTER JOIN 
    (Production.Products AS P
       INNER JOIN Production.Categories AS C
         ON C.categoryid = P.categoryid)
    ON S.supplierid = P.supplierid
WHERE S.country = N`Japan`;

RIGHT OUTER JOIN則與LEFT OUTER JOIN相反，根據ON條件和WHERE條件查詢表A和表B，查詢結果可以表A中資料為NULL。
FULL OUTER JOIN則只要表A和表B中任一表中有資料，結果都會被顯示出來。無論是表A為NULL，還是表B為NULL。
OUTER也是可以被省略的。LEFT JOIN就是LEFT OUTER JOIN的簡寫，相應的，RIGHT JOIN是RIGHT OUTER JOIN的簡寫，FULL JOIN是FULL OUTER JOIN的簡寫。

相關文章