SQLite的 UNION 子句/運算符用於合並兩個或多個 SELECT 語句的結果,不返回任何重復的行。
為了使用 UNION,每個 SELECT 被選擇的列數必須是相同的,相同數目的列表達式,相同的數據類型,並確保它們有相同的順序,但它們不必具有相同的長度。
UNION 的基本語法如下:
SELECT column1 [, column2 ] FROM table1 [, table2 ] [WHERE condition] UNION SELECT column1 [, column2 ] FROM table1 [, table2 ] [WHERE condition]
這裡給定的條件根據需要可以是任何表達式。
假設有下面兩個表,(1)COMPANY 表如下所示:
sqlite> select * from COMPANY; ID NAME AGE ADDRESS SALARY ---------- -------------------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0
(2)另一個表是 DEPARTMENT,如下所示:
ID DEPT EMP_ID ---------- -------------------- ---------- 1 IT Billing 1 2 Engineering 2 3 Finance 7 4 Engineering 3 5 Finance 4 6 Engineering 5 7 Finance 6
現在,讓我們使用 SELECT 語句及 UNION 子句來連接兩個表,如下所示:
sqlite> SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID UNION SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;
這將產生以下結果:
EMP_ID NAME DEPT ---------- -------------------- ---------- 1 Paul IT Billing 2 Allen Engineerin 3 Teddy Engineerin 4 Mark Finance 5 David Engineerin 6 Kim Finance 7 James Finance
UNION ALL 運算符用於結合兩個 SELECT 語句的結果,包括重復行。
適用於 UNION 的規則同樣適用於 UNION ALL 運算符。
UNION ALL 的基本語法如下:
SELECT column1 [, column2 ] FROM table1 [, table2 ] [WHERE condition] UNION ALL SELECT column1 [, column2 ] FROM table1 [, table2 ] [WHERE condition]
這裡給定的條件根據需要可以是任何表達式。
現在,讓我們使用 SELECT 語句及 UNION ALL 子句來連接兩個表,如下所示:
sqlite> SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID UNION ALL SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;
這將產生以下結果:
EMP_ID NAME DEPT ---------- -------------------- ---------- 1 Paul IT Billing 2 Allen Engineerin 3 Teddy Engineerin 4 Mark Finance 5 David Engineerin 6 Kim Finance 7 James Finance 1 Paul IT Billing 2 Allen Engineerin 3 Teddy Engineerin 4 Mark Finance 5 David Engineerin 6 Kim Finance 7 James Finance