當前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

SQL连接表(内连接、左连接、右连接、交叉连接、全外连接)

發(fā)布時間：2023/12/31 数据库 27 豆豆

生活随笔收集整理的這篇文章主要介紹了 SQL连接表(内连接、左连接、右连接、交叉连接、全外连接) 小編覺得挺不錯的,現(xiàn)在分享給大家,幫大家做個參考.

分享一些自己的連表總結

什么是連接表？

多表查詢原理：將多個表通過笛卡爾積形成一個虛表，再根據(jù)查詢條件篩選符合條件的數(shù)據(jù)。
在關系數(shù)據(jù)庫中，數(shù)據(jù)分布在多個邏輯表中。要獲得完整有意義的數(shù)據(jù)集，需要使用連接來查詢這些表
中的數(shù)據(jù)。 SQL Server支持多種
- 連接包括
  - INNER JOIN:內連接,關鍵字在表中存在至少一個匹配時返回行。
  - left join : 左連接，返回左表中所有的記錄以及右表中連接字段相等的記錄。
  - right join : 右連接，返回右表中所有的記錄以及左表中連接字段相等的記錄。
  - inner join : 內連接，又叫等值連接，只返回兩個表中連接字段相等的行。
  - full join : 外連接，返回兩個表中的行：left join + right join。
  - cross join : 結果是笛卡爾積，就是第一個表的行數(shù)乘以第二個表的行數(shù)。
  - GROUP BY:全外連接，子句必須放在 WHERE 子句中的條件之后，必須放在 ORDER BY 子句之前

每種連接類型指定SQL Server如何使用一個表中的數(shù)據(jù)來選擇另一個表中的行

A. SQL Server內連接

內連接是SQL Server中最常用的連接之一。 內部聯(lián)接子句用于查詢來自兩個或多個相關表的數(shù)據(jù)。
SQL Server INNER JOIN 子句的語法：
在此語法中，從 T1 和 T2 表中查詢檢索數(shù)據(jù)：
- 首先，在 FROM 子句中指定主表( T1 )
- 其次，在 INNER JOIN 子句和連接謂詞中指定第二個表( T2 )。只有連接謂詞計算為 TRUE 的行才包
  含在結果集中。

**INNER JOIN **子句將表 T1 的每一行與表 T2 的行進行比較，以查找滿足連接謂詞的所有行對。如果連接
謂詞的計算結果為 TRUE ，則匹配
的 T1 和 T2 行的列值將合并為一個新行并包含在結果集中。

下圖說明了兩個結果集的內聯(lián)接的結果：
使用兩個 INNER JOIN 子句來查詢三個表中的數(shù)據(jù)：

圖中左右兩邊的表是有關聯(lián)的，中間黃色的交互區(qū)就是他們兩個關聯(lián)表中關聯(lián)的數(shù)據(jù)。
使用INNER JOIN可以在兩個有關聯(lián)數(shù)據(jù)的表中把關聯(lián)的數(shù)據(jù)查詢出來

使用兩個 INNER JOIN 子句來查詢三個表中的數(shù)據(jù)：
INNER JOIN 關鍵字在表中存在至少一個匹配時返回行。

SELECT--在下面嗎三張表中需要查詢的數(shù)據(jù)product_name,category_name,brand_name, list_price FROM數(shù)據(jù)表(1) p -- p是數(shù)據(jù)表的引用變量，可以通過p.出數(shù)據(jù)表格(1)中的列名 --使用INNER JOIN通過數(shù)據(jù)表(1)category_id值查詢出對應在數(shù)據(jù)表(2)的關聯(lián)數(shù)據(jù) INNER JOIN 數(shù)據(jù)表(2) c ON c數(shù)據(jù)表(2).category_id = p數(shù)據(jù)表(1).category_id --使用INNER JOIN通過數(shù)據(jù)表(1)中brand_id值查詢出對應在數(shù)據(jù)表(3)的關聯(lián)數(shù)據(jù) INNER JOIN 數(shù)據(jù)表(3) b ON b數(shù)據(jù)表(3).brand_id = p數(shù)據(jù)表(1).brand_id ORDER BY --查詢結果升序排序product_name DESC;

B. SQL Server左連接

LEFT JOIN 子句用于查詢來自多個表的數(shù)據(jù)。它返回左表中的所有行和右表中的匹配行。
如果在右表中找不到匹配的行，則使用 NULL 代替顯示。

以下圖說明了兩個結果集的左連接結果：

以下說明如何使用 LEFT JOIN 子句來連接兩個表 student(學生表) 和 sc(成績表) ：

下面的小案例左表(學生表)通過某列名在右表(成績表)中查詢出在相同列名中的一行數(shù)據(jù)

SELECTs.sno,--學生表中的學生學號 c.cno,--成績表中的課程號c.score--成績表中學生的在此課程中的成績 FROM student s LEFT JOIN sc c ON--使用LEFT JOIN獲取sc(成績表)c.sno=s.sno; --通過student(學生ID)等于成績表中(學生ID)選擇到學生表在成績表中的數(shù)據(jù)

以下是上面左連接查詢語句的查詢結果:*

圖中為空的格子是在右表(成績表)中沒有該學生學號下的考試課程與成績，所以默認返回了一個NULL值

對于學生表中的每一行，查詢將其與成績表中的所有行進行比較。如果一對行導致連接謂詞計算為
TRUE ，則將組合這些行中的列值以形成新行，然后將其包含在結果集中。

如果左表( T1 )中的行沒有與來自 T2 表的任何匹配行，則查詢將左表中的行的列值與來自右表的每個列
值的 NULL 組合。

C. ON與WHERE子句

關鍵字 on

數(shù)據(jù)庫在通過連接兩張或多張表來返回記錄時，都會生成一張中間的臨時表，然后再將這張臨時表返回給用戶。
在使用 left jion 時，on 和 where 條件的區(qū)別如下：

1、 on 條件是在生成臨時表時使用的條件，它不管 on 中的條件是否為真，都會返回左邊表中的記錄。
2、where 條件是在臨時表生成好后，再對臨時表進行過濾的條件。這時已經沒有 left join 的含義（必須返回左邊表的記錄）了，條件不為真的就全部過濾掉。

假設有兩張表：

兩條SQL語句:

select * from tab1 left join tab2 on (tab1.size = tab2.size) where tab2.name='AAA' select * from tab1 left join tab2 on (tab1.size = tab2.size and tab2.name='AAA')

其實以上結果的關鍵原因就是left join、right join、full join的特殊性，不管 on 上的條件是否為真都會返回 left 或 right 表中的記錄，full 則具有 left 和 right 的特性的并集。 而 inner jion 沒這個特殊性，則條件放在 on 中和 where 中，返回的結果集是相同的。

D. SQL Server右連接

RIGHT JOIN 子句組合來自兩個或多個表的數(shù)據(jù)。

RIGHT JOIN 開始從**右表中選擇數(shù)據(jù)并與左表中的行
匹配。 RIGHT JOIN 返回一個結果集，該結果集包含右表中的所有行，無論是否具有左表中的匹配行。
如果右表中的行沒有來自右表的任何匹配行，則結果集中右表的列將使用 NULL 值。

以下是 RIGHT JOIN 的語法：

SELECTselect_list FROMT1 RIGHT JOIN T2 ON join_predicate; SQL

在此語法中， T1 是左表， T2 是右表。
請注意， RIGHT JOIN 和 RIGHT OUTER JOIN 是相同的。 OUTER 關鍵字是可選的。
下圖說明了 RIGHT JOIN 操作

橙色部分表示返回的結果集。
SQL Server RIGHT JOIN示例
以下語句返回 production.products 表中的產品名稱和 sales.order_items 所有 order_id ：

SELECTproduct_name,order_id FROMsales.order_items o RIGHT JOIN production.products p ON o.product_id = p.product_id ORDER BY order_id;

E. SQL Server 交叉連接

CROSS JOIN 連接兩個或多個不相關的表。
以下是兩個表的SQL Server CROSS JOIN 的語法：

SELECT select_list FROM T1 CROSS JOIN T2;

CROSS JOIN 將第一個表(T1)中的每一行與第二個表(T2)中的每一行連接起來。換句話說，交叉連接返回
兩個表中行的笛卡爾積。與INNER JOIN或LEFT JOIN不同，交叉連接不會在連接的表之間建立關系。

假設 T1 表包含三行： 1 , 2 和 3 ， T2 表包含三行： A ， B 和 C 。 CROSS JOIN 從第一個表(T1)獲取一行，然后為第二個表(T2)中的每一行創(chuàng)建一個新行。然后它對第一個
表(T1)中的下一行執(zhí)行相同操作，依此類推。

在此圖中， CROSS JOIN 總共創(chuàng)建了 9 行。通常，如果第一個表有 n 行，第二個表有 m 行，則交叉連接
將產生 n x m 行。

F. SQL Server自連接

自聯(lián)接用于將表連接到自身(同一個表)。它對于查詢分層數(shù)據(jù)或比較同一個表中的行很有用。
自聯(lián)接使用內連接或左連接子句。由于使用自聯(lián)接的查詢引用同一個表，因此表別名用于為查詢中的表
分配不同的名稱

請注意，如果在不使用表別名的情況下在查詢中多次引用同一個表，則會出現(xiàn)錯誤。

SELECTt1.sno,t2.sno FROMsc t1,sc t2

上面查詢語句中兩次引用表 T 。表別名 t1 和 t2 用于為 T 表分配不同的名稱。

staffs 表存儲員工信息，如身份證，名字，姓氏和電子郵件。它還有一個名為 manager_id 的列，用
于指定直接管理者。例如，員工 Mireya 向管理員者 Fabiola 匯報工作，因為 Mireya 的 manager_id
列中的值是 Fabiola 。 Fabiola 沒有經理，因為它的 manager_id 列是一個 NULL 值。
要獲取工作匯報關系，請使用自聯(lián)接，如以下查詢中所示：

SELECTe.first_name + ' ' + e.last_name employee, m.first_name + ' ' + m.last_name manager FROMsales.staffs e INNER JOIN sales.staffs m ON m.staff_id = e.manager_id ORDER BY manager;

在這個例子中，兩次引用了 staffs 表：一個是員工的 e ，另一個是管理者的 m 。
連接謂詞使用 e.manager_id 和 m.staff_id 列中的值匹配 employee 和 manager 關系。
由于 INNER JOIN 效應， employee 列沒有 Fabiola Jackson 。如果用 LEFT JOIN 子句替換 INNER JOIN 子句，將獲得在 employee 列中包含 Fabiola Jackson 的結果集

G. SQL Server全外鏈接?

FULL OUTER JOIN當左表或右表中存在匹配項時，該命令將返回所有行。
以下SQL語句選擇所有客戶和所有訂單：

SELECT Customers.CustomerName, Orders.OrderID FROM Customers FULL OUTER JOIN Orders ON Customers.CustomerID=Orders.CustomerID ORDER BY Customers.CustomerName;

注：FULL OUTER JOIN關鍵字返回左表（Customers）所有行，以及所有來自右表（Orders）的行。如
果“Customers”中的某些行在“Orders”中沒有匹配項，或者在“Orders”中的某些行在“Customers”中沒有
匹配項，則這些行也將被列出。

總結

以上是生活随笔為你收集整理的SQL连接表(内连接、左连接、右连接、交叉连接、全外连接)的全部內容，希望文章能夠幫你解決所遇到的問題。

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