sql派生表

『壹』 sql多表查詢總結

連接查詢包括合並、內連接、外連接和交叉連接，如果涉及多表查詢，了解這些連接的特點很重要。
只有真正了解它們之間的區別，才能正確使用。

UNION 操作符用於合並兩個或多個 SELECT 語句的結果集。

UNION 運算符通過組合其他兩個結果表（例如 TABLE1 和 TABLE2）並消去表中任何重復行而派生出一個結果表。

當 ALL 隨 UNION 一起使用時（即 UNION ALL），不消除重復行。兩種情況下，派生表的每一行不是來自 TABLE1 就是來自 TABLE2。

注意：使用UNION時，兩張表查詢的結果有相同數量的列、列類型相似。

學生表信息（Students）：

教師表信息（Teachers）：

1）基本UNION查詢，查詢學校教師、學生的總的信息表，包括ID和姓名

查詢結果：

2）查詢教師學生全部姓名
因為UNION只會選擇不同的值，如果學生中和教師中有重名的情況，這就需要UNION ALL

查詢結果：

INNER JOIN（內連接），也成為自然連接

作用：根據兩個或多個表中的列之間的關系，從這些表中查詢數據。

注意⚠️: 內連接是從結果中刪除其他被連接表中沒有匹配行的所有行，所以內連接可能會丟失信息。

重點：內連接，只查匹配行。

語法：(INNER可省略)

學生表信息（Students）：

專業信息表（Majors）：

實例：查詢學生信息，包括ID,姓名、專業名稱

查詢結果：

根據結果可以清晰看到，確實只有匹配的行。學生Lucy的信息丟失了。

與內連接相比，即使沒有匹配行，也會返回一個表的全集。

外連接分為三種：左外連接，右外連接，全外連接。
對應SQL：LEFT/RIGHT/FULL OUTER JOIN。
通常我們省略outer 這個關鍵字。寫成：LEFT/RIGHT/FULL JOIN。

重點：至少有一方保留全集，沒有匹配行用NULL代替。

1、LEFT JOIN (左連接)

結果集保留左表的所有行，但只包含第二個表與第一表匹配的行。第二個表相應的空行被放入NULL值。

依然沿用內鏈接的例子:

（1）使用左連接查詢學生的信息，其中包括學生ID，學生姓名和專業名稱。

查詢結果：

通過結果，我們可以看到左連接包含了第一張表的所有信息，在第二張表中如果沒有匹配項，則用NULL代替。

2、RIGHT JOIN (右連接)

右外連接保留了第二個表的所有行，但只包含第一個表與第二個表匹配的行。第一個表相應空行被入NULL值。

右連接與左連接思想類似。只是第二張保留全集，如果第一張表中沒有匹配項，用NULL代替

依然沿用內鏈接的例子，只是改為右連接

（2）使用右連接查詢學生的信息，其中包括學生ID，學生姓名和專業名稱

查詢結果：

通過結果可以看到，包含了第二張表Majors的全集，Computer在Students表中沒有匹配項，就用NULL代替。

3、FULL JOIN (全連接）

會把兩個表所有的行都顯示在結果表中

3)使用全連接查詢學生的信息，其中包括學生ID，學生姓名和專業名稱。

查詢結果：

包含了兩張表的所有記錄，沒有記錄丟失，沒有匹配的行用NULL代替。

4、CROSS JOIN（交叉連接）

交叉連接。交叉連接返回左表中的所有行，左表中的每一行與右表中的所有行組合。交叉連接也稱作笛卡爾積。

簡單查詢兩張表組合，這是求笛卡兒積，效率最低。

笛卡兒積：笛卡爾乘積，也叫直積。假設集合A={a,b}，集合B={0,1,2}，則兩個集合的笛卡爾積為{(a,0),(a,1),(a,2),(b,0),(b,1), (b,2)}。可以擴展到多個集合的情況。類似的例子有，如果A表示某學校學生的集合，B表示該學校所有課程的集合，則A與B的笛卡爾積表示所有可能的選課情況。

4）交叉連接查詢學生的信息，其中包括學生ID，學生姓名和專業名稱。

查詢結果：

5）查詢多表，其實也是笛卡兒積，與CROSS JOIN等價，以下查詢同上述結果一樣。

這個可能很常見，但是大家一定要注意了，這樣就查詢了兩張表中所有組合的全集。

查詢結果：

6）增加查詢條件

注意:在使用CROSS JOIN關鍵字交叉連接表時，因為生成的是兩個表的笛卡爾積，因而不能使用ON關鍵字，只能在WHERE子句中定義搜索條件。

查詢結果：

查詢結果與INNER JOIN一樣，但是其效率就慢很多了。

『貳』 SQL 查找兩表中不同的數據

使用except函數，select * from B where (select count(1) from A where A.ID = B.ID) = 0.

方法一（推薦）

WITH C AS ( SELECT Name

FROM ConsumerCouponApply A

WHERE NOT EXISTS ( SELECT 1

FROM ConsumerCouponApply B

WHERE B.Name = A.Name

GROUP BY B.Name )

GROUP BY A.Name

)

SELECT COUNT(1)

FROM C

方法二

WITH C AS ( SELECT A.Name

FROM ConsumerCouponApply A

GROUP BY A.Name

EXCEPT

SELECT B.Name

FROM ConsumerCouponApply B

GROUP BY B.Name

)

SELECT COUNT(1)

FROM C

方法三

SELECT COUNT(A.Name)

FROM ConsumerCouponApply A

LEFT JOIN ConsumerCouponApply B ON A.Name = B.Name

WHERE B.ID IS NULL

(2)sql派生表擴展閱讀：

高級查詢運算詞：

A： UNION 運算符：

UNION 運算符通過組合其他兩個結果表（例如 TABLE1 和 TABLE2）並消去表中任何重復行而派生出一個

結果表。當 ALL 隨 UNION 一起使用時（即 UNION ALL），不消除重復行。兩種情況下，派生表的每一行

不是來自 TABLE1 就是來自 TABLE2。

B： EXCEPT 運算符

EXCEPT 運算符通過包括所有在 TABLE1 中但不在 TABLE2 中的行並消除所有重復行而派生出一個結果表。當 ALL 隨 EXCEPT 一起使用時 (EXCEPT ALL)，不消除重復行。

C： INTERSECT 運算符

INTERSECT 運算符通過只包括 TABLE1 和 TABLE2 中都有的行並消除所有重復行而派生出一個結果表。當

ALL 隨 INTERSECT 一起使用時 (INTERSECT ALL)，不消除重復行。

註：使用運算詞的幾個查詢結果行必須是一致的。

『叄』 sql'x%'處理和 '%x%'有啥區別,派生表是什麼意思

'x%'是查詢匹配以'x'開頭的字元串
'%x%'是查詢匹配字元串中有'x'字元的字元串
例如：'x%'能夠匹配上'xa'
'%x%'能匹配上'axa'，'x%'就不行了

在多表查詢的時候，用到的子查詢就是派生表，就是把一個select 語句看作是一個表來進行操作