SQL Prompt教程：舊式聯接語法（ST001）

是一款實用的SQL語法提示工具。SQL Prompt根據資料庫的物件名稱、語法和程式碼片段自動進行檢索，為使用者提供合適的程式碼選擇。自動指令碼設定使程式碼簡單易讀--當開發者不大熟悉指令碼時尤其有用。SQL Prompt安裝即可使用，能大幅提高編碼效率。此外，使用者還可根據需要進行自定義，使之以預想的方式工作。

如果您對SQL Prompt感興趣，可以在慧都網免費下載最新試用版

使用舊式聯接語法沒有任何優勢。如果SQL提示標識了它在舊版程式碼中的使用，則重寫語句以使用ANSI標準的連線語法將簡化和改進程式碼。

SQL Prompt實現了一個靜態程式碼分析規則ST001，該規則將在開發和測試工作期間自動檢查程式碼是否出現非ANSI標準的JOIN語法。

SQL的“舊樣式” Microsoft / Sybase JOIN樣式已使用= *和* =語法，已棄用，不再使用。當資料庫引擎級別為10（SQL Server 2008）或更高版本（相容級別100）時，使用此語法的查詢將失敗。ANSI-89表引用列表（FROM表A，表B）仍然僅是INNER JOIN的ISO標準。這些樣式都不值得使用。自ANSI SQL-92釋出以來，就一直最好指定所需的連線型別：INNER，LEFT OUTER，RIGHT OUTER，FULL OUTER和CROSS。儘管可以選擇任何受支援的JOIN樣式，而不會影響SQL Server使用的查詢計劃，但使用ANSI標準語法將使您的程式碼更易於理解，更一致，並且可移植到其他關聯式資料庫系統中。

舊式外部聯接已過時

當SQL Server從Sybase派生時，它繼承了其舊的非標準Transact-SQL語法用於聯接，其中分別包括左和右外部聯接的=和=語法。

從第一個表（外部聯接的“外部成員”）中選擇的左外部聯接運算子* =，滿足語句限制的所有行。僅當該行的連線條件匹配時，第二個表（“內部成員”）才會生成值；否則，它提供空值。相反，對於右外部聯接運算子= *，第二個表成為“外部成員”，從中選擇所有符合條件的行。

即使支援這些語法，也存在一些限制。您不能在HAVING子句中包含Transact-SQL外部聯接，也不能在與舊式外部聯接相同的表示式中執行其他INNER JOIN。此外，外部連線語法（* =或= *）並不總是給出正確的結果，有時在指定外部連線時使用交叉連線。

無論如何，此語法在SQL Server 2005及更高版本中已被棄用，並在SQL Server 2008中停止工作。清單1的目的是查詢pubs資料庫，其目的是返回沒有相應作者的所有標題。

--all titles without an author
SELECT ti.title + Coalesce( ' (' + ti.type + ') -' + ti.pub_id,' (Unknown category)') AS publication
  FROM dbo.titles AS ti, dbo.titleauthor AS Ta
     where ti.title_id *= Ta.title_id
     AND Ta.title_id IS NULL;

清單1
但是，除非將相容性級別設定為90，否則它將在SQL Server 2008或更高版本中失敗。此設定僅在SQL Server 2012之前可用：

Msg 102, Level 15, State 1, Line 6
Incorrect syntax near '*='.

如果仍然有使用此語法的查詢，則在升級到SQL Server 2012之前，必須重寫它們以使用清單2中所示的ANSI標準語法，因為不再支援相容性級別90。

--all titles without an author
SELECT ti.title + Coalesce( ' (' + ti.type + ') -' + ti.pub_id,' (Unknown category)') AS publication
  FROM dbo.titles AS ti
    LEFT OUTER JOIN dbo.titleauthor AS Ta
      ON ti.title_id = Ta.title_id
  WHERE Ta.title_id IS NULL
ORDER BY ti.title

清單2

得到以下結果：

支援老式的內部聯接，但沒有優勢

內部聯接的表引用語法是ANSI標準的一部分，因此仍受支援。清單3使用了它，並將返回與其釋出者居住在同一城市的所有作者。

–（舊語法）與釋出者居住在同一城市的作者

  --(Old Syntax)authors that live in the same city as their publishers
SELECT 
  authors.au_fname + ' ' + authors.au_lname AS Author, 
  publishers.pub_name AS Publisher, publishers.city
  FROM dbo.authors, dbo.titleauthor, dbo.titles, dbo.publishers
  WHERE authors.au_id = titleauthor.au_id 
    AND titleauthor.title_id = titles.title_id 
    AND titles.pub_id = publishers.pub_id 
    AND publishers.city = authors.city

清單3

清單4顯示了使用ANSI-92標準的相同程式碼，其中我們使聯接型別明確。

--(Newer Syntax) authors that live in the same city as their publishers
SELECT 
  authors.au_fname+ ' '+authors.au_lname AS Author, 
  publishers.pub_name AS Publisher, publishers.city
  FROM dbo.authors
    INNER JOIN dbo.titleauthor
      ON authors.au_id = titleauthor.au_id
    INNER JOIN dbo.titles
      ON titleauthor.title_id = titles.title_id
    INNER JOIN dbo.publishers
      ON titles.pub_id = publishers.pub_id
  WHERE publishers.city = authors.city

清單4

兩者給出的結果相同，執行計劃相同。

但是，廣泛接受的是，舊式的“引文列表”內部聯接語法很難閱讀和理解，因此比新語法更容易出錯。

無論如何，即使仍支援該舊式語法，也沒有遺憾的理由。例如，您如何使用舊風格的聯接語法確定與釋出者居住在不同城市的作者所佔的百分比？這將是一個使用方括號和子查詢的外觀複雜的查詢。使用更新的語法，它很容易編寫，也很容易理解其邏輯。

--proportion of authors who live in the same city as their publishers
SELECT 
  Sum(CASE WHEN publishers.city IS NULL THEN 1 ELSE 0 END) AS [different city],
  Count(*) AS total, 
  (Sum(CASE WHEN publishers.city IS NULL THEN 1 ELSE 0 END) * 100)
      / Count(*) AS percentage
  FROM dbo.authors
    INNER JOIN dbo.titleauthor
      ON authors.au_id = titleauthor.au_id
    INNER JOIN dbo.titles
      ON titleauthor.title_id = titles.title_id
    LEFT OUTER JOIN dbo.publishers
      ON titles.pub_id = publishers.pub_id AND publishers.city = authors.city

清單5

結論

將舊式聯接語法留在遺留程式碼中沒有任何優勢。如果發現此程式碼異味，它將改進並簡化程式碼以重寫語句以使用ANSI標準連線語法 。