1、 SQL 是一種聲明式語言

首先要把這個概念記在腦中：“聲明”。 SQL 語言是為計算機聲明了一個你想從原始數據中獲得什么樣的結果的一個范例，而不是告訴計算機如何能夠得到結果。這是不是很棒？

（譯者注：簡單地說，SQL 語言聲明的是結果集的屬性，計算機會根據 SQL 所聲明的內容來從數據庫中挑選出符合聲明的數據，而不是像傳統編程思維去指示計算機如何操作。）

1	SELECTfirst_name, last_name FROMemployeesWHEREsalary > 100000

上面的例子很容易理解，我們不關心這些雇員記錄從哪里來，我們所需要的只是那些高薪者的數據（譯者注： salary>100000 ）。

我們從哪兒學習到這些？

如果 SQL 語言這么簡單，那么是什么讓人們“聞 SQL 色變”？主要的原因是：我們潛意識中的是按照命令式編程的思維方式思考問題的。就好像這樣：“電腦，先執行這一步，再執行那一步，但是在那之前先檢查一下是否滿足條件 A 和條件 B ”。例如，用變量傳參、使用循環語句、迭代、調用函數等等，都是這種命令式編程的思維慣式。

2、 SQL 的語法并不按照語法順序執行

SQL 語句有一個讓大部分人都感到困惑的特性，就是：SQL 語句的執行順序跟其語句的語法順序并不一致。SQL 語句的語法順序是：

• SELECT[DISTINCT]
• FROM
• WHERE
• GROUP BY
• HAVING
• UNION
• ORDER BY

為了方便理解，上面并沒有把所有的 SQL 語法結構都列出來，但是已經足以說明 SQL 語句的語法順序和其執行順序完全不一樣，就以上述語句為例，其執行順序為：

• FROM
• WHERE
• GROUP BY
• HAVING
• SELECT
• DISTINCT
• UNION
• ORDER BY

關于 SQL 語句的執行順序，有三個值得我們注意的地方：

1、 FROM 才是 SQL 語句執行的第一步，并非 SELECT 。數據庫在執行 SQL 語句的第一步是將數據從硬盤加載到數據緩沖區中，以便對這些數據進行操作。（譯者注：原文為“The first thing that happens is loading data from the disk into memory, in order to operate on such data.”，但是并非如此，以 Oracle 等常用數據庫為例，數據是從硬盤中抽取到數據緩沖區中進行操作。）

2、 SELECT 是在大部分語句執行了之后才執行的，嚴格的說是在 FROM 和 GROUP BY 之后執行的。理解這一點是非常重要的，這就是你不能在 WHERE 中使用在 SELECT 中設定別名的字段作為判斷條件的原因。

1 2 3

SELECTA.x + A.y ASz FROMA WHEREz = 10 -- z 在此處不可用，因為SELECT是最后執行的語句！

如果你想重用別名z，你有兩個選擇。要么就重新寫一遍 z 所代表的表達式：

1 2 3

SELECTA.x + A.y ASz FROMA WHERE(A.x + A.y) = 10

…或者求助于衍生表、通用數據表達式或者視圖，以避免別名重用。請看下文中的例子。

3、 無論在語法上還是在執行順序上， UNION 總是排在在 ORDER BY 之前。很多人認為每個 UNION 段都能使用 ORDER BY 排序，但是根據 SQL 語言標準和各個數據庫 SQL 的執行差異來看，這并不是真的。盡管某些數據庫允許 SQL 語句對子查詢（subqueries）或者派生表（derived tables）進行排序，但是這并不說明這個排序在 UNION 操作過后仍保持排序后的順序。

注意：并非所有的數據庫對 SQL 語句使用相同的解析方式。如 MySQL、PostgreSQL和 SQLite 中就不會按照上面第二點中所說的方式執行。

我們學到了什么？

既然并不是所有的數據庫都按照上述方式執行 SQL 預計，那我們的收獲是什么？我們的收獲是永遠要記得： SQL 語句的語法順序和其執行順序并不一致，這樣我們就能避免一般性的錯誤。如果你能記住 SQL 語句語法順序和執行順序的差異，你就能很容易的理解一些很常見的 SQL 問題。

當然，如果一種語言被設計成語法順序直接反應其語句的執行順序，那么這種語言對程序員是十分友好的，這種編程語言層面的設計理念已經被微軟應用到了 LINQ 語言中。

3、 SQL 語言的核心是對表的引用（table references）

由于 SQL 語句語法順序和執行順序的不同，很多同學會認為SELECT 中的字段信息是 SQL 語句的核心。其實真正的核心在于對表的引用。

根據 SQL 標準，FROM 語句被定義為：

1	<fromclause> ::= FROM<tablereference> [ { <comma> <tablereference> }... ]

FROM 語句的“輸出”是一張聯合表，來自于所有引用的表在某一維度上的聯合。我們們慢慢來分析：

1

FROMa, b

上面這句 FROM 語句的輸出是一張聯合表，聯合了表 a 和表 b 。如果 a 表有三個字段， b 表有 5 個字段，那么這個“輸出表”就有 8 （ =5+3）個字段。

這個聯合表里的數據是 a*b，即 a 和 b 的笛卡爾積。換句話說，也就是 a 表中的每一條數據都要跟 b 表中的每一條數據配對。如果 a 表有3 條數據， b 表有 5 條數據，那么聯合表就會有 15 （ =5*3）條數據。

FROM 輸出的結果被 WHERE 語句篩選后要經過 GROUP BY 語句處理，從而形成新的輸出結果。我們后面還會再討論這方面問題。

如果我們從集合論（關系代數）的角度來看，一張數據庫的表就是一組數據元的關系，而每個 SQL 語句會改變一種或數種關系，從而產生出新的數據元的關系（即產生新的表）。

我們學到了什么？

思考問題的時候從表的角度來思考問題提，這樣很容易理解數據如何在 SQL 語句的“流水線”上進行了什么樣的變動。

4、 靈活引用表能使 SQL 語句變得更強大

靈活引用表能使 SQL 語句變得更強大。一個簡單的例子就是 JOIN 的使用。嚴格的說 JOIN 語句并非是 SELECT 中的一部分，而是一種特殊的表引用語句。 SQL 語言標準中表的連接定義如下：

1 2 3 4

<tablereference> ::= ????<tablename> ??| <derived table> ??| <joined table>

就拿之前的例子來說：

1

FROMa, b

a 可能輸如下表的連接：

1	a1JOINa2ONa1.id = a2.id

將它放到之前的例子中就變成了：

1	FROMa1JOINa2ONa1.id = a2.id, b

盡管將一個連接表用逗號跟另一張表聯合在一起并不是常用作法，但是你的確可以這么做。結果就是，最終輸出的表就有了 a1+a2+b 個字段了。

（譯者注：原文這里用詞為 degree ，譯為維度。如果把一張表視圖化，我們可以想象每一張表都是由橫縱兩個維度組成的，橫向維度即我們所說的字段或者列，英文為columns；縱向維度即代表了每條數據，英文為 record ，根據上下文，作者這里所指的應該是字段數。）

在 SQL 語句中派生表的應用甚至比表連接更加強大，下面我們就要講到表連接。

我們學到了什么？

思考問題時，要從表引用的角度出發，這樣就很容易理解數據是怎樣被 SQL 語句處理的，并且能夠幫助你理解那些復雜的表引用是做什么的。

更重要的是，要理解 JOIN 是構建連接表的關鍵詞，并不是 SELECT 語句的一部分。有一些數據庫允許在 INSERT 、 UPDATE 、 DELETE 中使用 JOIN 。

5、 SQL 語句中推薦使用表連接

我們先看看剛剛這句話：

1

FROMa, b

高級 SQL 程序員也許學會給你忠告：盡量不要使用逗號來代替 JOIN 進行表的連接，這樣會提高你的 SQL 語句的可讀性，并且可以避免一些錯誤。

利用逗號來簡化 SQL 語句有時候會造成思維上的混亂，想一下下面的語句：

1 2 3 4 5

FROMa, b, c, d, e, f, g, h WHEREa.a1 = b.bx ANDa.a2 = c.c1 ANDd.d1 = b.bc -- etc...

我們不難看出使用 JOIN 語句的好處在于：

• 安全。 JOIN 和要連接的表離得非常近，這樣就能避免錯誤。

• 更多連接的方式，JOIN 語句能去區分出來外連接和內連接等。

我們學到了什么？

記著要盡量使用 JOIN 進行表的連接，永遠不要在 FROM 后面使用逗號連接表。

6、 SQL 語句中不同的連接操作

SQL 語句中，表連接的方式從根本上分為五種：

• EQUI JOIN
• SEMI JOIN
• ANTI JOIN
• CROSS JOIN
• DIVISION

EQUI JOIN

這是一種最普通的 JOIN 操作，它包含兩種連接方式：

• INNER JOIN（或者是 JOIN ）
• OUTER JOIN（包括： LEFT 、 RIGHT、 FULL OUTER JOIN）

用例子最容易說明其中區別：

1 2 3 4 5 6 7 8 9 10

-- This table reference contains authors and their books. -- There is one record for each book and its author. -- authors without books are NOT included authorJOINbookONauthor.id = book.author_id -- This table reference contains authors and their books -- There is one record for each book and its author. -- ... OR there is an "empty" record for authors without books -- ("empty" meaning that all book columns are NULL) authorLEFTOUTERJOIN book ONauthor.id = book.author_id

SEMI JOIN

這種連接關系在 SQL 中有兩種表現方式：使用 IN，或者使用 EXISTS?！?SEMI ”在拉丁文中是“半”的意思。這種連接方式是只連接目標表的一部分。這是什么意思呢？再想一下上面關于作者和書名的連接。我們想象一下這樣的情況：我們不需要作者 / 書名這樣的組合，只是需要那些在書名表中的書的作者信息。那我們就能這么寫：

1 2 3 4 5 6 7

-- Using IN FROMauthor WHEREauthor.idIN(SELECTbook.author_idFROMbook) -- Using EXISTS FROMauthor WHEREEXISTS (SELECT1FROMbookWHEREbook.author_id = author.id)

盡管沒有嚴格的規定說明你何時應該使用 IN ，何時應該使用 EXISTS ，但是這些事情你還是應該知道的：

• IN比 EXISTS 的可讀性更好

• EXISTS 比IN 的表達性更好（更適合復雜的語句）

• 二者之間性能沒有差異（但對于某些數據庫來說性能差異會非常大）

因為使用 INNER JOIN 也能得到書名表中書所對應的作者信息，所以很多初學者機會認為可以通過 DISTINCT 進行去重，然后將 SEMI JOIN 語句寫成這樣：

1 2 3 4

-- Find only those authors who also have books SELECTDISTINCTfirst_name, last_name FROMauthor JOINbookONauthor.id = book.author_id

這是一種很糟糕的寫法，原因如下：

• SQL 語句性能低下：因為去重操作（ DISTINCT ）需要數據庫重復從硬盤中讀取數據到內存中。（譯者注： DISTINCT 的確是一種很耗費資源的操作，但是每種數據庫對于 DISTINCT 的操作方式可能不同）。

• 這么寫并非完全正確：盡管也許現在這么寫不會出現問題，但是隨著 SQL 語句變得越來越復雜，你想要去重得到正確的結果就變得十分困難。

更多的關于濫用 DISTINCT 的危害可以參考這篇博文

（http://blog.jooq.org/2013/07/30/10-common-mistakes-java-developers-make-when-writing-sql/）。

ANTI JOIN

這種連接的關系跟 SEMI JOIN 剛好相反。在 IN 或者 EXISTS 前加一個 NOT 關鍵字就能使用這種連接。舉個例子來說，我們列出書名表里沒有書的作者：

1 2 3 4 5 6 7

-- Using IN FROMauthor WHEREauthor.idNOTIN(SELECTbook.author_idFROMbook) -- Using EXISTS FROMauthor WHERENOTEXISTS (SELECT1FROMbookWHEREbook.author_id = author.id)

關于性能、可讀性、表達性等特性也完全可以參考 SEMI JOIN。

這篇博文介紹了在使用 NOT IN 時遇到 NULL 應該怎么辦，因為有一點背離本篇主題，就不詳細介紹，有興趣的同學可以讀一下

（http://blog.jooq.org/2012/01/27/sql-incompatibilities-not-in-and-null-values/）。

CROSS JOIN

這個連接過程就是兩個連接的表的乘積：即將第一張表的每一條數據分別對應第二張表的每條數據。我們之前見過，這就是逗號在 FROM 語句中的用法。在實際的應用中，很少有地方能用到 CROSS JOIN，但是一旦用上了，你就可以用這樣的 SQL語句表達：

1 2	-- Combine every author with every book authorCROSSJOINbook

DIVISION

DIVISION 的確是一個怪胎。簡而言之，如果 JOIN 是一個乘法運算，那么 DIVISION 就是 JOIN 的逆過程。DIVISION 的關系很難用 SQL 表達出來，介于這是一個新手指南，解釋 DIVISION 已經超出了我們的目的。但是有興趣的同學還是可以來看看這三篇文章

（http://blog.jooq.org/2012/03/30/advanced-sql-relational-division-in-jooq/）

（http://en.wikipedia.org/wiki/Relational_algebra#Division）

（https://www.simple-talk.com/sql/t-sql-programming/divided-we-stand-the-sql-of-relational-division/）。

推薦閱讀 →_→?《畫圖解釋SQL聯合語句》

我們學到了什么？

學到了很多！讓我們在腦海中再回想一下。 SQL 是對表的引用， JOIN 則是一種引用表的復雜方式。但是 SQL 語言的表達方式和實際我們所需要的邏輯關系之間是有區別的，并非所有的邏輯關系都能找到對應的 JOIN 操作，所以這就要我們在平時多積累和學習關系邏輯，這樣你就能在以后編寫 SQL 語句中選擇適當的 JOIN 操作了。

7、 SQL 中如同變量的派生表

在這之前，我們學習到過 SQL 是一種聲明性的語言，并且 SQL 語句中不能包含變量。但是你能寫出類似于變量的語句，這些就叫做派生表：

說白了，所謂的派生表就是在括號之中的子查詢：

1 2	-- A derived table FROM(SELECT*FROMauthor)

需要注意的是有些時候我們可以給派生表定義一個相關名（即我們所說的別名）。

1 2	-- A derived table with an alias FROM(SELECT*FROMauthor) a

派生表可以有效的避免由于 SQL 邏輯而產生的問題。舉例來說：如果你想重用一個用 SELECT 和 WHERE 語句查詢出的結果，這樣寫就可以（以 Oracle 為例）：

1 2 3 4 5 6 7 8

-- Get authors' first and last names, and their age in days SELECTfirst_name, last_name, age FROM( ??SELECTfirst_name, last_name, current_date- date_of_birth age ??FROMauthor ) -- If the age is greater than 10000 days WHEREage > 10000

需要我們注意的是：在有些數據庫，以及 SQL ： 1990 標準中，派生表被歸為下一級——通用表語句（ common table experssion）。這就允許你在一個 SELECT 語句中對派生表多次重用。上面的例子就（幾乎）等價于下面的語句：

1 2 3 4 5 6 7

WITHaAS( ??SELECTfirst_name, last_name, current_date- date_of_birth age ??FROMauthor ) SELECT* FROMa WHEREage > 10000

當然了，你也可以給“ a ”創建一個單獨的視圖，這樣你就可以在更廣泛的范圍內重用這個派生表了。更多信息可以閱讀下面的文章（http://en.wikipedia.org/wiki/View_%28SQL%29）。

我們學到了什么？

我們反復強調，大體上來說 SQL 語句就是對表的引用，而并非對字段的引用。要好好利用這一點，不要害怕使用派生表或者其他更復雜的語句。

8、 SQL 語句中 GROUP BY 是對表的引用進行的操作

讓我們再回想一下之前的 FROM 語句：

1

FROMa, b

現在，我們將 GROUP BY 應用到上面的語句中：

1	GROUPBYA.x, A.y, B.z

上面語句的結果就是產生出了一個包含三個字段的新的表的引用。我們來仔細理解一下這句話：當你應用 GROUP BY 的時候， SELECT 后沒有使用聚合函數的列，都要出現在 GROUP BY 后面。（譯者注：原文大意為“當你是用 GROUP BY 的時候，你能夠對其進行下一級邏輯操作的列會減少，包括在 SELECT 中的列”）。

• 需要注意的是：其他字段能夠使用聚合函數：

1 2 3

SELECTA.x, A.y, SUM(A.z) FROMA GROUPBYA.x, A.y

• 還有一點值得留意的是： MySQL 并不堅持這個標準，這的確是令人很困惑的地方。（譯者注：這并不是說 MySQL 沒有 GROUP BY 的功能）但是不要被 MySQL 所迷惑。 GROUP BY 改變了對表引用的方式。你可以像這樣既在 SELECT 中引用某一字段，也在 GROUP BY 中對其進行分組。

我們學到了什么？

GROUP BY，再次強調一次，是在表的引用上進行了操作，將其轉換為一種新的引用方式。

9、 SQL 語句中的 SELECT 實質上是對關系的映射

我個人比較喜歡“映射”這個詞，尤其是把它用在關系代數上。（譯者注：原文用詞為 projection ，該詞有兩層含義，第一種含義是預測、規劃、設計，第二種意思是投射、映射，經過反復推敲，我覺得這里用映射能夠更直觀的表達出 SELECT 的作用）。一旦你建立起來了表的引用，經過修改、變形，你能夠一步一步的將其映射到另一個模型中。 SELECT 語句就像一個“投影儀”，我們可以將其理解成一個將源表中的數據按照一定的邏輯轉換成目標表數據的函數。

通過 SELECT語句，你能對每一個字段進行操作，通過復雜的表達式生成所需要的數據。

SELECT 語句有很多特殊的規則，至少你應該熟悉以下幾條：

你僅能夠使用那些能通過表引用而得來的字段；

如果你有 GROUP BY 語句，你只能夠使用 GROUP BY 語句后面的字段或者聚合函數；

當你的語句中沒有 GROUP BY 的時候，可以使用開窗函數代替聚合函數；

當你的語句中沒有 GROUP BY 的時候，你不能同時使用聚合函數和其它函數；

有一些方法可以將普通函數封裝在聚合函數中；

……

一些更復雜的規則多到足夠寫出另一篇文章了。比如：為何你不能在一個沒有 GROUP BY 的 SELECT 語句中同時使用普通函數和聚合函數？（上面的第 4 條）

原因如下：

憑直覺，這種做法從邏輯上就講不通。

如果直覺不能夠說服你，那么語法肯定能。 SQL : 1999 標準引入了 GROUPING SETS，SQL： 2003 標準引入了 group sets : GROUP BY() 。無論什么時候，只要你的語句中出現了聚合函數，而且并沒有明確的 GROUP BY 語句，這時一個不明確的、空的 GROUPING SET 就會被應用到這段 SQL 中。因此，原始的邏輯順序的規則就被打破了，映射（即 SELECT ）關系首先會影響到邏輯關系，其次就是語法關系。（譯者注：這段話原文就比較艱澀，可以簡單理解如下：在既有聚合函數又有普通函數的 SQL 語句中，如果沒有 GROUP BY 進行分組，SQL 語句默認視整張表為一個分組，當聚合函數對某一字段進行聚合統計的時候，引用的表中的每一條 record 就失去了意義，全部的數據都聚合為一個統計值，你此時對每一條 record 使用其它函數是沒有意義的）。

糊涂了？是的，我也是。我們再回過頭來看點淺顯的東西吧。

我們學到了什么？

SELECT 語句可能是 SQL 語句中最難的部分了，盡管他看上去很簡單。其他語句的作用其實就是對表的不同形式的引用。而 SELECT 語句則把這些引用整合在了一起，通過邏輯規則將源表映射到目標表，而且這個過程是可逆的，我們可以清楚的知道目標表的數據是怎么來的。

想要學習好 SQL 語言，就要在使用 SELECT 語句之前弄懂其他的語句，雖然 SELECT 是語法結構中的第一個關鍵詞，但它應該是我們最后一個掌握的。

10、 SQL 語句中的幾個簡單的關鍵詞： DISTINCT ， UNION ， ORDER BY 和 OFFSET

在學習完復雜的 SELECT 豫劇之后，我們再來看點簡單的東西：

• 集合運算（ DISTINCT 和 UNION ）

• 排序運算（ ORDER BY，OFFSET…FETCH）

集合運算（ set operation）：

集合運算主要操作在于集合上，事實上指的就是對表的一種操作。從概念上來說，他們很好理解：

• DISTINCT 在映射之后對數據進行去重

• UNION 將兩個子查詢拼接起來并去重

• UNION ALL 將兩個子查詢拼接起來但不去重

• EXCEPT 將第二個字查詢中的結果從第一個子查詢中去掉

• INTERSECT 保留兩個子查詢中都有的結果并去重

排序運算（ ordering operation）：

排序運算跟邏輯關系無關。這是一個 SQL 特有的功能。排序運算不僅在 SQL 語句的最后，而且在 SQL 語句運行的過程中也是最后執行的。使用 ORDER BY 和 OFFSET…FETCH 是保證數據能夠按照順序排列的最有效的方式。其他所有的排序方式都有一定隨機性，盡管它們得到的排序結果是可重現的。

OFFSET…SET是一個沒有統一確定語法的語句，不同的數據庫有不同的表達方式，如 MySQL 和 PostgreSQL 的 LIMIT…OFFSET、SQL Server 和 Sybase 的 TOP…START AT 等。具體關于 OFFSET..FETCH 的不同語法可以參考這篇文章

（http://www.jooq.org/doc/3.1/manual/sql-building/sql-statements/select-statement/limit-clause/）。

讓我們在工作中盡情的使用 SQL！

正如其他語言一樣，想要學好 SQL 語言就要大量的練習。上面的 10 個簡單的步驟能夠幫助你對你每天所寫的 SQL 語句有更好的理解。另一方面來講，從平時常見的錯誤中也能積累到很多經驗。下面的兩篇文章就是介紹一些 JAVA 和其他開發者所犯的一些常見的 SQL 錯誤：

• 10 Common Mistakes Java Developers Make when Writing SQL
• 10 More Common Mistakes Java Developers Make when Writing SQL

======================原版=====http://blog.jobbole.com/55086/#comment-42518============================================

10 Easy Steps to a Complete Understanding of SQL

Too many programmers think SQL is a bit of a beast. It is one of the few?declarative languages?out there, and as such, behaves in an entirely different way from imperative, object-oriented, or even functional languages (although, some say that SQL is alsosomewhat functional).

I'm writing SQL every day and embracing SQL with myOpen Source Software jOOQ. I thus feel compelled to bring the beauty of SQL a bit closer to those of you still struggling with it. The following tutorial is destined for

• readers who have already worked with SQL but never completely understood it
• readers who know SQL well but have never really thought about its syntax
• readers who want to teach SQL to others

This tutorial will focus on SELECT statements only. Other DML statements will be covered in another tutorial. Here are...

10 Easy Steps to a Complete Understanding of SQL.

1. SQL is declarative

Get this into your head first. Declarative. The only paradigm where you "just" declare the nature of the results that you would like to get. Not?how?your computer shall compute those results. Isn't that wonderful?

SELECT first_name, last_name FROM employees WHERE salary > 100000

Easy to understand. You don't care where employee records physically come from. You just want those that have a decent salary.

What do we learn from this?

So if this is so simple, what's the problem? The problem is that most of us intuitively think in terms of?imperative programming. As in:?"machine, do this, and then do that, but before, run a check and fail if this-and-that". This includes storing temporary results in variables, writing loops, iterating, calling functions, etc. etc.

Forget about all that. Think about?how to declare?things. Not about?how to tell the machine?to compute things.

2. SQL syntax is not "well-ordered"

A common source of confusion is the simple fact that SQL syntax elements are not ordered in the way they are executed. The lexical ordering is:

• SELECT [ DISTINCT ]
• FROM
• WHERE
• GROUP BY
• HAVING
• UNION
• ORDER BY

For simplicity, not all SQL clauses are listed. This lexical ordering differs fundamentally from the logical order, i.e. from the order of execution:

• FROM
• WHERE
• GROUP BY
• HAVING
• SELECT
• DISTINCT
• UNION
• ORDER BY

There are three things to note:

FROM is the first clause, not SELECT. The first thing that happens is loading data from the disk into memory, in order to operate on such data.

SELECT is executed after most other clauses. Most importantly, after FROM and GROUP BY. This is important to understand when you think you can reference stuff that you declare in the SELECT clause from the WHERE clause. The following is not possible:

SELECT A.x + A.y AS z FROM A WHERE z = 10 -- z is not available here!

If you wanted to reuse?z, you have two options. Either repeat the expression:

SELECT A.x + A.y AS z FROM A WHERE (A.x + A.y) = 10

... or you resort to derived tables, common table expressions, or views to avoid code repetition. See examples further down.

UNION is placed before ORDER BY in both lexical and logical ordering. Many people think that each UNION subselect can be ordered, but according to the SQL standard and most SQL dialects, that is not true. While some dialects allow for orderingsubqueries?or?derived tables, there is no guarantee that such ordering will be retained after a UNION operation

Note, not all databases implement things the same way. Rule number 2, for instance, does not apply exactly in the above way to MySQL, PostgreSQL, and SQLite.

What do we learn from this?

Always remember both the?lexical order?and the?logical order?of SQL clauses to avoid very common mistakes. If you understand that distinction, it will become very obvious why some things work and others don't.

Of course, it would have been nice if the language was designed in a way that the?lexical order?actually reflected the?logical order, as it is implemented in Microsoft's?LINQ.

3. SQL is about table references

Because of the difference between?lexical ordering?and?logical ordering, most beginners are probably tricked into thinking that column values are the first-class citizens in SQL. They are not. The most important things are table references.

The?SQL standard?defines the FROM clause as such:

<from clause> ::= FROM <table reference> [ { <comma> <table reference> }... ]

The "output" of the FROM clause is a combined table reference of the combined degree of all table references. Let's digest this, slowly.

FROM a, b

The above produces a combined table reference of the degree of?a?+ the degree of?b. If?a?has 3 columns and?b?has 5 columns, then the "output table" will have 8 (3 + 5) columns.

The records contained in this combined table reference are those of the cross product / cartesian product of?a x b. In other words, each record of?a?is paired with each record of?b. If?a?has 3 records and?b?has 5 records, then the above combined table reference will produce 15 records (3 x 5).

This "output" is "fed" / "piped" into the GROUP BY clause (after filtering in the WHERE clause), where it is transformed into a new "output". We'll deal with that later on.

If we're looking at these things from a?relational algebra?/?set theory?perspective, a SQL table is a?relation?or a?set of tuples. And each SQL clause will transform one or several relations in order to produce new relations.

What do we learn from this?

Always think in terms of table references to understand how data is "pipelined" through your SQL clauses.

4. SQL table references can be rather powerful

A table reference is something rather powerful. A simple example of their power is the JOIN keyword, which is actually not part of the SELECT statement, but part of a "special" table reference. The joined table, as defined in the?SQL standard?(simplified):

<table reference> ::=<table name>| <derived table>| <joined table>

If we take again the example from before:

FROM a, b

a?can be a joined table as such:

a1 JOIN a2 ON a1.id = a2.id

Expanding this into the previous expression, we'd get:

FROM a1 JOIN a2 ON a1.id = a2.id, b

While it is discouraged to combine the comma-separated list of table references syntax with the joined table syntax, you can most certainly do this. The resulting, combined table reference will now have a degree of?a1+a2+b.

Derived tables are even more powerful than joined tables. We'll get to that.

What do we learn from this?

Always, always think in terms of table references. Not only is this important to understand how data is "pipelined" through your SQL clauses (see previous section), it will also help you understand how complex table references are constructed.

And, importantly, understand that JOIN is a keyword for constructing joined tables. Not a part of the SELECT statement. Some databases allow for using JOIN in INSERT, UPDATE, DELETE

5. SQL JOIN tables should be used rather than comma-separated tables

Before, we've seen this clause:

FROM a, b

Advanced SQL developers will probably tell you that it is discouraged to use the comma-separated list at all, and always fully express your JOINs. This will help you improve readability of your SQL statement, and thus prevent mistakes.

One very common mistake is to forget a JOIN predicate somewhere. Think about the following:

FROM a, b, c, d, e, f, g, h WHERE a.a1 = b.bx AND a.a2 = c.c1 AND d.d1 = b.bc -- etc...

The join table syntax is both

• Safer, as you can place join predicates close to the joined tables, thus preventing mistakes.
• More expressive, as you can distinguish between OUTER JOIN, INNER JOIN, etc.

What do we learn from this?

Always use JOIN. Never use comma-separated table references in your FROM clauses.

6. SQL's different JOIN operations

JOIN operations essentially come with five flavours:

• EQUI JOIN
• SEMI JOIN
• ANTI JOIN
• CROSS JOIN
• DIVISION

These terms are commonly used in?relational algebra. SQL uses different terms for the above concepts, if they exist at all. Let's have a closer look:

EQUI JOIN

This is the most common JOIN operation. It has two sub-flavours:

• INNER JOIN (or just JOIN)
• OUTER JOIN (further sub-flavoured as LEFT, RIGHT, FULL OUTER JOIN)

The difference is best explained by example:

-- This table reference contains authors and their books. -- There is one record for each book and its author. -- authors without books are NOT included author JOIN book ON author.id = book.author_id-- This table reference contains authors and their books -- There is one record for each book and its author. -- ... OR there is an "empty" record for authors without books -- ("empty" meaning that all book columns are NULL) author LEFT OUTER JOIN book ON author.id = book.author_id

SEMI JOIN

This relational concept can be expressed in two ways in SQL: Using an IN predicate, or using an EXISTS predicate. "Semi" means "half" in latin. This type of join is used to join only "half" of a table reference. What does that mean? Consider again the above joining of author and book. Let's imagine that we don't want author/book combinations, but just those authors who actually also have books. Then we can write:

-- Using IN FROM author WHERE author.id IN (SELECT book.author_id FROM book)-- Using EXISTS FROM author WHERE EXISTS (SELECT 1 FROM book WHERE book.author_id = author.id)

While there is no general rule as to whether you should prefer IN or EXISTS, these things can be said:

• IN predicates tend to be more readable than EXISTS predicates
• EXISTS predicates tend to be more expressive than IN predicates (i.e. it is easier to express very complex SEMI JOIN)
• There is no formal difference in performance. There may, however, be a?huge performance difference on some databases.

Because INNER JOIN also produces only those authors that actually have books, many beginners may think that they can then remove duplicates using DISTINCT. They think they can express a SEMI JOIN like this:

-- Find only those authors who also have books SELECT DISTINCT first_name, last_name FROM author JOIN book ON author.id = book.author_id

This is very bad practice for two reasons:

• It is very slow, as the database has to load a lot of data into memory, just to remove duplicates again.
• It is not entirely correct, even if it produces the correct result in this simple example. But as soon as you JOIN more table references, you will have a very hard time correctly removing duplicates from your results.

Some more information about abuse of DISTINCT can be?seen in this blog post.

ANTI JOIN

This relational concept is just the opposite of a SEMI JOIN. You can produce it simply by adding a NOT keyword to the IN or EXISTS predicates. An example, where we'll select those authors who do not have any books:

-- Using IN FROM author WHERE author.id NOT IN (SELECT book.author_id FROM book)-- Using EXISTS FROM author WHERE NOT EXISTS (SELECT 1 FROM book WHERE book.author_id = author.id)

The same rules with respect to performance, readability, expressivity apply. However, there is a small caveat with respect to NULLs when using NOT IN,?which is a bit out of scope for this tutorial.

CROSS JOIN

This produces a cross product of the two joined table references, combining every record of the first table reference with every record of the second table reference. We have seen before, that this can be achieved with comma-separated table references in the FROM clause. In the rare cases where this is really desired, you can also write a CROSS JOIN explicitly, in most SQL dialects:

-- Combine every author with every book author CROSS JOIN book

DIVISION

The relational division is really a beast of its own breed. In short, if JOIN is multiplication, division is the inverse of JOIN. Relational divisions are very tough to express in SQL. As this is a beginners' tutorial, explaining it is out of scope. For the brave among you,?read on about it here,?here,?and here.

What do we learn from this?

A lot. Again, let's hammer this into our heads. SQL is about table references. Joined tables are quite sophisticated table references. But there is a difference in relational-speak and SQL-speak. Not all relational join operations are also formal SQL join operations. With a bit of practice and knowledge about relational theory, you will always be able to choose the right type of relational JOIN and be able to translate it to the correct SQL.

7. SQL's derived tables are like table variables

Before, we've learned that SQL is a?declarative language, and as such, variables do not have a place (they do in some SQL dialects, though). But you can write something?likevariables. And those beasts are called derived tables.

A derived table is nothing but a subquery wrapped in parentheses.

-- A derived table FROM (SELECT * FROM author)

Note that some SQL dialects require derived tables to have a?correlation name?(also known as alias).

-- A derived table with an alias FROM (SELECT * FROM author) a

Derived tables are awesome when you want to circumvent the problems caused by thelogical ordering?of SQL clauses. For instance, if you want to reuse a column expression in both the SELECT and the WHERE clause, just write (Oracle dialect):

-- Get authors' first and last names, and their age in days SELECT first_name, last_name, age FROM (SELECT first_name, last_name, current_date - date_of_birth ageFROM author ) -- If the age is greater than 10000 days WHERE age > 10000

Note that some databases, and the SQL:1999 standard have taken derived tables to the next level, introducing?common table expressions. This will allow you to reuse the samederived table?several times within a single SQL SELECT statement. The above query would then translate to the (almost) equivalent:

WITH a AS (SELECT first_name, last_name, current_date - date_of_birth ageFROM author ) SELECT * FROM a WHERE age > 10000

Obviously, you could also externalise "a" into a standalone view for even broader reuse of common SQL subselects.?Read more about views here.

What do we learn from this?

Again, again, again. SQL is mostly about table references, not columns. Make use of them. Don't be afraid of writing derived tables or other complex table references.

8. SQL GROUP BY transforms previous table references

Let's reconsider our previous FROM clause:

FROM a, b

And now, let's apply a GROUP BY clause to the above combined table reference

GROUP BY A.x, A.y, B.z

The above produces a new table reference with only three remaining columns (!). Let's digest this again. If you apply GROUP BY, then you reduce the number of available columns in all subsequent logical clauses - including SELECT. This is the syntactical reason why you can only reference columns from the GROUP BY clause in the SELECT clause.

• Note that other columns may still be available as arguments of aggregate functions:

  SELECT A.x, A.y, SUM(A.z) FROM A GROUP BY A.x, A.y

• Note that?MySQL, unfortunately, doesn't adhere to this standard, causing nothing but confusion. Don't fall for MySQL's tricks. GROUP BY transforms table references. You can thus only reference columns also referenced in the GROUP BY clause.

What do we learn from this?

GROUP BY, again, operates on table references, transforming them into a new form.

9. SQL SELECT is called projection in relational algebra

I personally like the term "projection", as it is used in relational algebra. Once you've generated your table reference, filtered it, transformed it, you can step to projecting it to another form. The SELECT clause is like a projector. A table function making use of arow value expression?to transform each record from the previously constructed table reference into the final outcome.

Within the SELECT clause, you can finally operate on columns, creating complex column expressions as parts of the record / row.

There are a lot of special rules with respect to the nature of available expressions, functions, etc. Most importantly, you should remember these:

You can only use column references that can be produced from the "output" table reference

If you have a GROUP BY clause, you may only reference columns from that clause, or aggregate functions.

You can use window functions instead of aggregate functions, when you don't have a GROUP BY clause.

If you don't have a GROUP BY clause, you must not combine aggregate functions with non-aggregate functions.

There are some rules with respect to wrapping regular functions in aggregate functions and vice-versa.

There are ...

Well, there are lots of complex rules. They could fill yet another tutorial. For instance, the reason why you cannot combine aggregate functions with non-aggregate functions in the projection of a SELECT statement without GROUP BY clause (rule number 4) is this:

It doesn't make sense. Intuitively.

If intuition doesn't help (it hardly does, with a SQL beginner), then syntax rules do. SQL:1999 introduced GROUPING SETS, and SQL:2003 introduced empty grouping sets: GROUP BY (). Whenever an aggregate function is present, and there is no explicit GROUP BY clause, an implicit, empty GROUPING SET is applied (rule number 2). Hence, the original rules about?logical ordering?aren't exactly true anymore, and the projection (SELECT) influences the outcome of a logically preceding, yet lexically succeeding clause (GROUP BY).

Confused? Yes. Me too. Let's get back to simpler things.

What do we learn from this?

The SELECT clause may be one of the most complex clauses in SQL, even if it appears so simple. All other clauses just "pipe" table references from one to another. The SELECT clause messes up the beauty of these table references, by completely transforming them, applying some rules to them retroactively.

In order to understand SQL, it is important to understand everything?else?first, before trying to tackle SELECT. Even if SELECT is the first clause in lexical ordering, it should be the last.

10. SQL DISTINCT, UNION, ORDER BY, and OFFSET are simple again

After the complicated SELECT, we can get back to simple things again:

• Set operations (DISTINCT and UNION)
• Ordering operations (ORDER BY, OFFSET .. FETCH)

Set operations

Set operations operate on "sets", which are actually nothing other than... tables. Well, almost. Conceptually, they're easy to understand.

• DISTINCT removes duplicates?after?the projection.
• UNION concatenates two subselects and removes duplicates
• UNION ALL concatenates two subselects retaining duplicates
• EXCEPT removes records from the first subselect that are also contained in the second subselect (and then removes duplicates)
• INTERSECT retains only records contained in both subselects (and then removes duplicates)

All of this removing duplicates is usually non-sense. Most often, you should just use UNION ALL, when you want to concatenate subselects.

Ordering operations

Ordering is not a relational feature. It is a SQL-only feature. It is applied at the very end of both?lexical ordering?and?logical ordering?of your SQL statement. Using ORDER BY and OFFSET .. FETCH is the only way to guarantee that records can be accessed by index in a reliable way. All other ordering is always arbitrary and random, even if it may appear to be reproducible.

OFFSET .. FETCH is only one syntax variant. Other variants include MySQL's and PostgreSQL's LIMIT .. OFFSET, or SQL Server's and Sybase's TOP .. START AT. A good overview of various ways to implement OFFSET .. FETCH?can be seen here.

Let's get to work

As with every language, SQL takes a lot of practice to master. The above 10 simple steps will help you make more sense of the every day SQL that you're writing. On the other hand, it is also good to learn from common mistakes. The following two articles list lots of common mistakes Java (and other) developers make when writing SQL:

• 10 Common Mistakes Java Developers Make when Writing SQL
• 10 More Common Mistakes Java Developers Make when Writing SQL

總結

以上是生活随笔為你收集整理的10个简单步骤，完全理解SQL的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。