深入理解SQL的四種連接-左外連接、右外連接、內連接、全連接

来源:http://www.cnblogs.com/JsonZhangAA/archive/2016/04/05/5355360.html
-Advertisement-
Play Games

轉載至http://www.jb51.net/article/39432.htm 本篇文章是對SQL中的四種連接-左外連接、右外連接、內連接、全連接進行了詳細的分析介紹,需要的朋友參考下 本篇文章是對SQL中的四種連接-左外連接、右外連接、內連接、全連接進行了詳細的分析介紹,需要的朋友參考下 1、內 ...


轉載至http://www.jb51.net/article/39432.htm

本篇文章是對SQL中的四種連接-左外連接、右外連接、內連接、全連接進行了詳細的分析介紹,需要的朋友參考下

1、內聯接(典型的聯接運算,使用像 =  或 <> 之類的比較運算符)。包括相等聯接和自然聯接。    
內聯接使用比較運算符根據每個表共有的列的值匹配兩個表中的行。例如,檢索 students和courses表中學生標識號相同的所有行。  
   
2、外聯接。
外聯接可以是左向外聯接、右向外聯接或完整外部聯接。    
在 FROM子句中指定外聯接時,可以由下列幾組關鍵字中的一組指定:    
1)LEFT  JOIN或LEFT OUTER JOIN    
左向外聯接的結果集包括  LEFT OUTER子句中指定的左表的所有行,而不僅僅是聯接列所匹配的行。如果左表的某行在右表中沒有匹配行,則在相關聯的結果集行中右表的所有選擇列表列均為空值。      
2)RIGHT  JOIN 或 RIGHT  OUTER  JOIN    
右向外聯接是左向外聯接的反向聯接。將返回右表的所有行。如果右表的某行在左表中沒有匹配行,則將為左表返回空值。      
3)FULL  JOIN 或 FULL OUTER JOIN
完整外部聯接返回左表和右表中的所有行。當某行在另一個表中沒有匹配行時,則另一個表的選擇列表列包含空值。如果表之間有匹配行,則整個結果集行包含基表的數據值。  

3、交叉聯接  
交叉聯接返回左表中的所有行,左表中的每一行與右表中的所有行組合。交叉聯接也稱作笛卡爾積。   
FROM 子句中的表或視圖可通過內聯接或完整外部聯接按任意順序指定;但是,用左或右向外聯接指定表或視圖時,表或視圖的順序很重要。有關使用左或右向外聯接排列表的更多信息,請參見使用外聯接。    

例子:  
-------------------------------------------------
  a表     id   name     b表     id   job   parent_id  
              1   張3                   1     23     1  
              2   李四                 2     34     2  
              3   王武                 3     34     4      
  a.id同parent_id   存在關係  
--------------------------------------------------   
 1) 內連接  
  select   a.*,b.*   from   a   inner   join   b     on   a.id=b.parent_id      
  結果是    
  1   張3                   1     23     1  
  2   李四                  2     34     2  

  2)左連接  
  select   a.*,b.*   from   a   left   join   b     on   a.id=b.parent_id      
  結果是    
  1   張3                   1     23     1  
  2   李四                  2     34     2  
  3   王武                  null  

 3) 右連接  
  select   a.*,b.*   from   a   right   join   b     on   a.id=b.parent_id      
  結果是    
  1   張3                   1     23     1  
  2   李四                  2     34     2  
  null                       3     34     4  

 4) 完全連接  
  select   a.*,b.*   from   a   full   join   b     on   a.id=b.parent_id  
  結果是    
  1   張3                  1     23     1  
  2   李四                 2     34     2  
  null                   3     34     4  
  3   王武                 null
--------------------------------------------------------------------------------------------一、交叉連接(CROSS JOIN)
交叉連接(CROSS JOIN):有兩種,顯式的和隱式的,不帶ON子句,返回的是兩表的乘積,也叫笛卡爾積。
例如:下麵的語句1和語句2的結果是相同的。

語句1:隱式的交叉連接,沒有CROSS JOIN。
SELECT O.ID, O.ORDER_NUMBER, C.ID, C.NAME
FROM ORDERS O , CUSTOMERS C
WHERE O.ID=1;

語句2:顯式的交叉連接,使用CROSS JOIN。
SELECT O.ID,O.ORDER_NUMBER,C.ID,
C.NAME
FROM ORDERS O CROSS JOIN CUSTOMERS C
WHERE O.ID=1;
語句1和語句2的結果是相同的,查詢結果如下:

二、內連接(INNER JOIN)
內連接(INNER JOIN):有兩種,顯式的和隱式的,返回連接表中符合連接條件和查詢條件的數據行。(所謂的鏈接表就是資料庫在做查詢形成的中間表)。
例如:下麵的語句3和語句4的結果是相同的。

語句3:隱式的內連接,沒有INNER JOIN,形成的中間表為兩個表的笛卡爾積。
SELECT O.ID,O.ORDER_NUMBER,C.ID,C.NAME
FROM CUSTOMERS C,ORDERS O
WHERE C.ID=O.CUSTOMER_ID;

語句4:顯示的內連接,一般稱為內連接,有INNER JOIN,形成的中間表為兩個表經過ON條件過濾後的笛卡爾積。
SELECT O.ID,O.ORDER_NUMBER,C.ID,C.NAME
FROM CUSTOMERS C INNER JOIN ORDERS O ON C.ID=O.CUSTOMER_ID;
語句3和語句4的查詢結果:

三、外連接(OUTER JOIN):外連不但返回符合連接和查詢條件的數據行,還返回不符合條件的一些行。外連接分三類:左外連接(LEFT OUTER JOIN)、右外連接(RIGHT OUTER JOIN)和全外連接(FULL OUTER JOIN)。
三者的共同點是都返回符合連接條件和查詢條件(即:內連接)的數據行。不同點如下:
左外連接還返回左表中不符合連接條件單符合查詢條件的數據行。
右外連接還返回右表中不符合連接條件單符合查詢條件的數據行。
全外連接還返回左表中不符合連接條件單符合查詢條件的數據行,並且還返回右表中不符合連接條件單符合查詢條件的數據行。全外連接實際是上左外連接和右外連接的數學合集(去掉重覆),即“全外=左外 UNION 右外”。
說明:左表就是在“(LEFT OUTER JOIN)”關鍵字左邊的表。右表當然就是右邊的了。在三種類型的外連接中,OUTER 關鍵字是可省略的。

下麵舉例說明:
語句5:左外連接(LEFT OUTER JOIN)
SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME
FROM ORDERS O LEFT OUTER JOIN CUSTOMERS C ON C.ID=O.CUSTOMER_ID;

語句6:右外連接(RIGHT OUTER JOIN)
SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME
FROM ORDERS O RIGHT OUTER JOIN CUSTOMERS C ON C.ID=O.CUSTOMER_ID;
註意:WHERE條件放在ON後面查詢的結果是不一樣的。例如:

語句7:WHERE條件獨立。
SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME
FROM ORDERS O LEFT OUTER JOIN CUSTOMERS C ON C.ID=O.CUSTOMER_ID
WHERE O.ORDER_NUMBER<>'MIKE_ORDER001';

語句8:將語句7中的WHERE條件放到ON後面。
SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME
FROM ORDERS O LEFT OUTER JOIN CUSTOMERS C ON C.ID=O.CUSTOMER_ID AND O.ORDER_NUMBER<>'MIKE_ORDER001';

從語句7和語句8查詢的結果來看,顯然是不相同的,語句8顯示的結果是難以理解的。因此,推薦在寫連接查詢的時候,ON後面只跟連接條件,而對中間表限制的條件都寫到WHERE子句中。

語句9:全外連接(FULL OUTER JOIN)。
SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME
FROM ORDERS O FULL OUTER JOIN CUSTOMERS C ON C.ID=O.CUSTOMER_ID;
註意:MySQL是不支持全外的連接的,這裡給出的寫法適合Oracle和DB2。但是可以通過左外和右外求合集來獲取全外連接的查詢結果。下圖是上面SQL在Oracle下執行的結果:

語句10:左外和右外的合集,實際上查詢結果和語句9是相同的。
SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME
FROM ORDERS O LEFT OUTER JOIN CUSTOMERS C ON C.ID=O.CUSTOMER_ID
UNION
SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME
FROM ORDERS O RIGHT OUTER JOIN CUSTOMERS C ON C.ID=O.CUSTOMER_ID;
語句9和語句10的查詢結果是相同的,如下:

四、聯合連接(UNION JOIN):這是一種很少見的連接方式。Oracle、MySQL均不支持,其作用是:找出全外連接和內連接之間差異的所有行。這在數據分析中排錯中比較常用。也可以利用資料庫的集合操作來實現此功能。
語句11:聯合查詢(UNION JOIN)例句,還沒有找到能執行的SQL環境。
SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME
FROM ORDERS O UNION JOIN CUSTOMERS C ON C.ID=O.CUSTOMER_ID

語句12:語句11在DB2下的等價實現。還不知道DB2是否支持語句11呢!
SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME
FROM ORDERS O FULL OUTER JOIN CUSTOMERS C ON C.ID=O.CUSTOMER_ID
EXCEPT
SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME
FROM ORDERS O INNER JOIN CUSTOMERS C ON C.ID=O.CUSTOMER_ID;

語句13:語句11在Oracle下的等價實現。
SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME
FROM ORDERS O FULL OUTER JOIN CUSTOMERS C ON C.ID=O.CUSTOMER_ID
MINUS
SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME
FROM ORDERS O INNER JOIN CUSTOMERS C ON C.ID=O.CUSTOMER_ID;
查詢結果如下:

五、自然連接(NATURAL INNER JOIN):說真的,這種連接查詢沒有存在的價值,既然是SQL2標準中定義的,就給出個例子看看吧。自然連接無需指定連接列,SQL會檢查兩個表中是否相同名稱的列,且假設他們在連接條件中使用,並且在連接條件中僅包含一個連接列。不允許使用ON語句,不允許指定顯示列,顯示列只能用*表示(ORACLE環境下測試的)。對於每種連接類型(除了交叉連接外),均可指定NATURAL。下麵給出幾個例子。
語句14:
SELECT *
FROM ORDERS O NATURAL INNER JOIN CUSTOMERS C;

語句15:
SELECT *
FROM ORDERS O NATURAL LEFT OUTER JOIN CUSTOMERS C;

語句16:
SELECT *
FROM ORDERS O NATURAL RIGHT OUTER JOIN CUSTOMERS C;

語句17:
SELECT *
FROM ORDERS O NATURAL FULL OUTER JOIN CUSTOMERS C;

六、SQL查詢的基本原理:兩種情況介紹。
第一、
單表查詢:根據WHERE條件過濾表中的記錄,形成中間表(這個中間表對用戶是不可見的);然後根據SELECT的選擇列選擇相應的列進行返回最終結果。

第二、兩表連接查詢:對兩表求積(笛卡爾積)並用ON條件和連接連接類型進行過濾形成中間表;然後根據WHERE條件過濾中間表的記錄,並根據SELECT指定的列返回查詢結果。

第三、
多表連接查詢:先對第一個和第二個表按照兩表連接做查詢,然後用查詢結果和第三個表做連接查詢,以此類推,直到所有的表都連接上為止,最終形成一個中間的結果表,然後根據WHERE條件過濾中間表的記錄,並根據SELECT指定的列返回查詢結果。
理解SQL查詢的過程是進行SQL優化的理論依據。

七、ON後面的條件(ON條件)和WHERE條件的區別:
ON條件:是過濾兩個鏈接表笛卡爾積形成中間表的約束條件。
WHERE條件:在有ON條件的SELECT語句中是過濾中間表的約束條件。在沒有ON的單表查詢中,是限制物理表或者中間查詢結果返回記錄的約束。在兩表或多表連接中是限制連接形成最終中間表的返回結果的約束。
從這裡可以看出,將WHERE條件移入ON後面是不恰當的。推薦的做法是:
ON只進行連接操作,WHERE只過濾中間表的記錄。

八、總結
連接查詢是SQL查詢的核心,連接查詢的連接類型選擇依據實際需求。如果選擇不當,非但不能提高查詢效率,反而會帶來一些邏輯錯誤或者性能低下。下麵總結一下兩表連接查詢選擇方式的依據:
1、 查兩表關聯列相等的數據用內連接。
2、 Col_L是Col_R的子集時用右外連接。
3、 Col_R是Col_L的子集時用左外連接。
4、 Col_R和Col_L彼此有交集但彼此互不為子集時候用全外。
5、 求差操作的時候用聯合查詢。
多個表查詢的時候,這些不同的連接類型可以寫到一塊。例如:
SELECT T1.C1,T2.CX,T3.CY
FROM TAB1 T1
       INNER JOIN TAB2 T2 ON (T1.C1=T2.C2)
       INNER JOIN TAB3 T3 ON (T1.C1=T2.C3)
       LEFT OUTER JOIN TAB4 ON(T2.C2=T3.C3);
WHERE T1.X >T3.Y;
上面這個SQL查詢是多表連接的一個示範。


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 在保密你的伺服器和數據,防備當前複雜的攻擊,SQL Server有你需要的一切。但在你能有效使用這些安全功能前,你需要理解你面對的威脅和一些基本的安全概念。這篇文章提供了基礎,因此你可以對SQL Server里的安全功能充分利用,不用在面對特定威脅,不能保護你數據的功能上浪費時間。 從讓人眼花繚亂的 ...
  • 1、SELECT基本語句: 2、檢索所有欄位,用"*"替換欄位名,這會導致效率低下 3、去除重覆項,“DISTINCT”作用於所有欄位 4、若檢索結果過多,可限定結果的條數 5、對檢索出的數據進行排序,ASC為升序,也為預設排序方式;DESC為降序。ORDER BY 語句一定是最後一條語句。如果不排 ...
  • 上節回顧 一、數據類型 什麼是數據類型? 數據類型是指列、存儲過程參數、表達式和局部變數的數據特征,它決定了數據的存儲格式,代表了不同的信息類型。 所謂數據類型,最直接的理解就是我們有些是存儲數字的,而數字當中,我們有時是要存儲為整數,有些可能要存儲為小數,還有的可能要存儲為日期、字元型,這就是我們 ...
  • MySQL的記錄長度 MySQL預設規定一條記錄最大的長度是65535位元組,所有的欄位加在一起所占的位元組數不能超過65535。但是MySQL中欄位的長度有的時使用位元組來規定int,有些欄位類型是使用字元個數來規定的。 1個漢字所占的是多少?根據字元編碼而定, latin1字元集1個字母占據1個位元組。 ...
  • AVG() 函數返回數值列的平均值。 SQL AVG() 語法 演示資料庫 在本教程中,我們將使用眾所周知的 Northwind 樣本資料庫。 下麵是選自 "Products" 表的數據: SQL AVG() 實例 下麵的 SQL 語句從 "Products" 表的 "Price" 列獲取平均值: ...
  • 資料庫的操作 創建一個資料庫 語法: create database 資料庫名【charset utf8】; 示例: 創建資料庫的本質:會在my.ini中的datadir配置選項指定的目錄中創建一個與資料庫同名的目錄。 在資料庫同名目錄中會存在一個db.opt文本文件示例: 說明: 字元集,是用於存 ...
  • INSERT DELAYED 語法 用於INSERT語句的DELAYED選項是MySQL相對於標準SQL的擴展。如果您的客戶端不能等待INSERT完成,則這個選項是非常有用的。當您使用MySQL進行日誌編寫時,這是非常常見的問題。您也可以定期運行SELECT和UPDATE語句,這些語句花費的時間較長 ...
  • 其實距離此次電面過去有幾天了,因為之前一直忙論文開題的事情,沒有時間整理,今天抽時間總結一下。 先說一句,百度的部門真的太多了,好像很多部門都有自己的數據開發團隊……具體不太瞭解,我這次面的是互聯網數據研發部的大數據研發崗位。要求語言主要是Python,但是我主要使用Java,之前對Python也是 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發(一):使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說,近來想嘗試開發一款安卓APP,考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP,畢竟是使用Visual Studio開發工具,使用起來也比較的順手,結合微軟官方的教程進行了安卓 ...
  • 前言 QuestPDF 是一個開源 .NET 庫,用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫,它徹底改變了我們生成 PDF 文檔的方 ...
  • 項目地址 項目後端地址: https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址: ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址: http://tree ...
  • 話不多說,直接開乾 一.下載 1.官方鏈接下載: https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe,運行開始下載SQL server; 二. ...
  • 前言 隨著物聯網(IoT)技術的迅猛發展,MQTT(消息隊列遙測傳輸)協議憑藉其輕量級和高效性,已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫,為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog支持多種接收器用於日誌存儲,增強器用於添加屬性,LogContext管理動態屬性,支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項,適用於不同需求。 ...
  • 目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據,爬取信息時需要模擬瀏覽器行為,否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下: 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • 1.前言 什麼是熱更新 游戲或者軟體更新時,無需重新下載客戶端進行安裝,而是在應用程式啟動的情況下,在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR,Xlua,ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles,Addressable, ...
  • 本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺,其實像阿裡雲,騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後,它會送 ...
  • 通過以下方式可以高效,並保證數據同步的可靠性 1.API設計 使用RESTful設計,確保API端點明確,並使用適當的HTTP方法(如POST用於創建,PUT用於更新)。 設計清晰的請求和響應模型,以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證,確保接收到的數據符合預期格 ...