SQL 如何使用自聯結、自然聯結和外聯結

来源:https://www.cnblogs.com/vin-c/archive/2022/05/18/16283354.html
-Advertisement-
Play Games

本文介紹自聯結（self-join）、自然聯結（natural join）和外聯結 （outer join），包括它們的含義和使用方法。介紹如何使用表別名，如何對被聯結的表使用聚集函數。 一、使用表別名 SQL 如何創建計算欄位 介紹瞭如何使用別名引用被檢索的表列。給列起別名的語法如下： SELEC ...


目錄

本文介紹自聯結（self-join）、自然聯結（natural join）和外聯結 （outer join），包括它們的含義和使用方法。介紹如何使用表別名，如何對被聯結的表使用聚集函數。

一、使用表別名

SQL 如何創建計算欄位 介紹瞭如何使用別名引用被檢索的表列。給列起別名的語法如下：

SELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')'
        AS vend_title
FROM Vendors
ORDER BY vend_name;

SQL 除了可以對列名和計算欄位使用別名，還允許給表名起別名。這樣做有兩個主要理由：

  • 縮短 SQL 語句；
  • 允許在一條 SELECT 語句中多次使用相同的表。

請看下麵的 SELECT 語句。它與 如何使用 SQL INNER JOIN 聯結兩個或多個表 例子中所用的語句基本相同，但改成了使用別名：

SELECT cust_name, cust_contact
FROM Customers AS C, Orders AS O, OrderItems AS OI
WHERE C.cust_id = O.cust_id
 AND OI.order_num = O.order_num
 AND prod_id = 'RGAN01';

可以看到，FROM 子句中的三個表全都有別名。Customers AS C 使用 C 作為 Customers 的別名，如此等等。

這樣，就可以使用省略的 C 而不用全名 Customers。

在這個例子中，表別名只用於 WHERE 子句。其實它不僅能用於 WHERE 子句，還可以用於 SELECT 的列表、ORDER BY 子句以及其他語句部分。

註意：Oracle 中沒有 AS

Oracle 不支持 AS 關鍵字。

要在 Oracle 中使用別名，可以不用 AS，簡單地指定列名即可（因此，應該是 Customers C，而不是 Customers AS C）。

需要註意，表別名只在查詢執行中使用。與列別名不一樣，表別名不返回到客戶端。

二、使用不同類型的聯結

迄今為止，我們使用的只是內聯結或等值聯結的簡單聯結。現在來看三種其他聯結：自聯結（self-join）、自然聯結（natural join）和外聯結 （outer join）。

2.1 自聯結

如前所述，使用表別名的一個主要原因是能在一條 SELECT 語句中不止一次引用相同的表。下麵舉一個例子。

假如要給與 Jim Jones 同一公司的所有顧客發送一封信件。這個查詢要求首先找出 Jim Jones 工作的公司，然後找出在該公司工作的顧客。

下麵是解決此問題的一種方法：

SELECT cust_id, cust_name, cust_contact
FROM Customers
WHERE cust_name = (SELECT cust_name
                   FROM Customers
                   WHERE cust_contact = 'Jim Jones');

輸出：

cust_id      cust_name          cust_contact
--------     --------------     --------------
1000000003   Fun4All            Jim Jones
1000000004   Fun4All            Denise L. Stephens

這是第一種解決方案，使用了子查詢。內部的 SELECT 語句做了一個簡單檢索，返回 Jim Jones 工作公司的 cust_name。

該名字用於外部查詢的 WHERE 子句中，以檢索出為該公司工作的所有雇員（SQL 如何使用子查詢 中介紹了子查詢，更多信息請參閱此文章）。

現在來看使用聯結的相同查詢：

SELECT c1.cust_id, c1.cust_name, c1.cust_contact
FROM Customers AS c1, Customers AS c2
WHERE c1.cust_name = c2.cust_name
 AND c2.cust_contact = 'Jim Jones';

輸出：

cust_id     cust_name       cust_contact
-------     -----------     --------------
1000000003  Fun4All         Jim Jones
1000000004  Fun4All         Denise L. Stephens

提示：Oracle 中沒有 AS

Oracle 用戶應該記住去掉 AS。

此查詢中需要的兩個表實際上是相同的表，因此 Customers 表在 FROM 子句中出現了兩次。

雖然這是完全合法的，但對 Customers 的引用具有歧義性，因為 DBMS 不知道你引用的是哪個 Customers 表。

解決此問題，需要使用表別名。Customers 第一次出現用了別名 c1，第二次出現用了別名 c2。現在可以將這些別名用作表名。

例如，SELECT 語句使用 c1 首碼明確給出所需列的全名。

如果不這樣，DBMS 將返回錯誤，因為名為 cust_id、cust_name、cust_contact 的列各有兩個。DBMS 不知道想要的是哪一列（即使它們其實是同一列）。

WHERE 首先聯結兩個表，然後按第二個表中的 cust_contact 過濾數據，返回所需的數據。

提示：用自聯結而不用子查詢

自聯結通常作為外部語句，用來替代從相同表中檢索數據的使用子查詢語句。

雖然最終的結果是相同的，但許多 DBMS 處理聯結遠比處理子查詢快得多。

應該試一下兩種方法，以確定哪一種的性能更好。

2.2 自然聯結

無論何時對錶進行聯結，應該至少有一列不止出現在一個表中（被聯結的列）。

標準的聯結（如何使用 SQL INNER JOIN 聯結兩個或多個表 中介紹的內聯結）返回所有數據，相同的列甚至多次出現。

自然聯結排除多次出現，使每一列只返回一次。

怎樣完成這項工作呢？答案是，系統不完成這項工作，由你自己完成它。

自然聯結要求你只能選擇那些唯一的列，一般通過對一個表使用通配符（SELECT *），而對其他表的列使用明確的子集來完成。下麵舉一個例子：

SELECT C.*, O.order_num, O.order_date,
       OI.prod_id, OI.quantity, OI.item_price
FROM Customers AS C, Orders AS O,
     OrderItems AS OI
WHERE C.cust_id = O.cust_id
 AND OI.order_num = O.order_num
 AND prod_id = 'RGAN01';

提示：Oracle 中沒有 AS

Oracle 用戶應該記住去掉 AS。

在這個例子中，通配符只對第一個表使用。所有其他列明確列出，所以沒有重覆的列被檢索出來。

事實上，我們迄今為止建立的每個內聯結都是自然聯結，很可能永遠都不會用到不是自然聯結的內聯結。

2.3 外聯結

許多聯結將一個表中的行與另一個表中的行相關聯，但有時候需要包含沒有關聯行的那些行。例如，可能需要使用聯結完成以下工作：

  • 對每個顧客下的訂單進行計數，包括那些至今尚未下訂單的顧客；
  • 列出所有產品以及訂購數量，包括沒有人訂購的產品；
  • 計算平均銷售規模，包括那些至今尚未下訂單的顧客。

在上述例子中，聯結包含了那些在相關表中沒有關聯行的行。這種聯結稱為外聯結。

註意：語法差別

需要註意，用來創建外聯結的語法在不同的 SQL 實現中可能稍有不同。

下麵段落中描述的各種語法形式覆蓋了大多數實現，在繼續學習之前請參閱你使用的 DBMS 文檔，以確定其語法。

下麵的 SELECT 語句給出了一個簡單的內聯結。它檢索所有顧客及其訂單：

SELECT Customers.cust_id, Orders.order_num
FROM Customers
 INNER JOIN Orders ON Customers.cust_id = Orders.cust_id;

外聯結語法類似。要檢索包括沒有訂單顧客在內的所有顧客，可如下進行：

SELECT Customers.cust_id, Orders.order_num
FROM Customers
 LEFT OUTER JOIN Orders ON Customers.cust_id = Orders.cust_id;

輸出：

cust_id        order_num
----------     ---------
1000000001     20005
1000000001     20009
1000000002     NULL
1000000003     20006
1000000004     20007
1000000005     20008

類似 如何使用 SQL INNER JOIN 聯結兩個或多個表 提到的內聯結，這條 SELECT 語句使用了關鍵字 OUTER JOIN 來指定聯結類型（而不是在 WHERE 子句中指定）。

但是，與內聯結關聯兩個表中的行不同的是，外聯結還包括沒有關聯行的行。

在使用 OUTER JOIN 語法時，必須使用 RIGHT 或 LEFT 關鍵字指定包括其所有行的表（RIGHT 指出的是 OUTER JOIN 右邊的表，而 LEFT 指出的是 OUTER JOIN 左邊的表）。

上面的例子使用 LEFT OUTER JOIN 從 FROM 子句左邊的表（Customers 表）中選擇所有行。

為了從右邊的表中選擇所有行，需要使用 RIGHT OUTER JOIN，如下例所示：

SELECT Customers.cust_id, Orders.order_num
FROM Customers
 RIGHT OUTER JOIN Orders ON Customers.cust_id = Orders.cust_id;

註意：SQLite 外聯結

SQLite 支持 LEFT OUTER JOIN，但不支持 RIGHT OUTER JOIN。

幸好，如果你確實需要在 SQLite 中使用 RIGHT OUTER JOIN，有一種更簡單的辦法，這將在下麵的提示中介紹。

提示：外聯結的類型

要記住，總是有兩種基本的外聯結形式：左外聯結和右外聯結。

它們之間的唯一差別是所關聯的表的順序。

換句話說，調整 FROM 或 WHERE 子句中表的順序，左外聯結可以轉換為右外聯結。

因此，這兩種外聯結可以互換使用，哪個方便就用哪個。

還存在另一種外聯結，就是全外聯結（full outer join），它檢索兩個表中的所有行並關聯那些可以關聯的行。

與左外聯結或右外聯結包含一個表的不關聯的行不同，全外聯結包含兩個表的不關聯的行。全外聯結的語法如下：

SELECT Customers.cust_id, Orders.order_num
FROM Customers
 FULL OUTER JOIN Orders ON Customers.cust_id = Orders.cust_id;

註意：FULL OUTER JOIN 的支持

MariaDB、MySQL 和 SQLite 不支持 FULL OUTER JOIN 語法。

三、使用帶聚集函數的聯結

如 如何使用 SQL AVG、COUNT、MAX、MIN 和 SUM 彙總數據 所述，聚集函數用來彙總數據。

雖然至今為止我們舉的聚集函數的例子都只是從一個表中彙總數據，但這些函數也可以與聯結一起使用。

我們來看個例子，要檢索所有顧客及每個顧客所下的訂單數，下麵的代碼使用 COUNT() 函數完成此工作：

SELECT Customers.cust_id,
       COUNT(Orders.order_num) AS num_ord
FROM Customers
 INNER JOIN Orders ON Customers.cust_id = Orders.cust_id
GROUP BY Customers.cust_id;

輸出：

cust_id        num_ord
----------     --------
1000000001     2
1000000003     1
1000000004     1
1000000005     1

這條 SELECT 語句使用 INNER JOIN 將 Customers 和 Orders 表互相關聯。

GROUP BY 子句按顧客分組數據，因此，函數調用 COUNT(Orders.order_num) 對每個顧客的訂單計數，將它作為 num_ord 返回。

聚集函數也可以方便地與其他聯結一起使用。請看下麵的例子：

SELECT Customers.cust_id,
       COUNT(Orders.order_num) AS num_ord
FROM Customers
 LEFT OUTER JOIN Orders ON Customers.cust_id = Orders.cust_id
GROUP BY Customers.cust_id;

輸出：

cust_id        num_ord
----------     -------
1000000001     2
1000000002     0
1000000003     1
1000000004     1
1000000005     1

這個例子使用左外部聯結來包含所有顧客，甚至包含那些沒有任何訂單的顧客。結果中也包含了顧客 1000000002，他有 0 個訂單，這和使用 INNER JOIN 時不同。

四、使用聯結和聯結條件

在總結討論聯結的這兩篇前，有必要彙總一下聯結及其使用的要點。

  • 註意所使用的聯結類型。一般我們使用內聯結，但使用外聯結也有效。
  • 關於確切的聯結語法，應該查看具體的文檔，看相應的 DBMS 支持何種語法（大多數 DBMS 使用這兩篇中描述的某種語法）。
  • 保證使用正確的聯結條件（不管採用哪種語法），否則會返回不正確的數據。
  • 應該總是提供聯結條件，否則會得出笛卡兒積。
  • 在一個聯結中可以包含多個表，甚至可以對每個聯結採用不同的聯結類型。雖然這樣做是合法的，一般也很有用，但應該在一起測試它們前分別測試每個聯結。這會使故障排除更為簡單。

五、小結

本文是 如何使用 SQL INNER JOIN 聯結兩個或多個表 的延續，首先介紹瞭如何以及為什麼使用別名，然後討論不同的聯結類型以及每類聯結所使用的語法。

我們還介紹瞭如何與聯結一起使用聚集函數，以及在使用聯結時應該註意的問題。

原文鏈接：https://www.developerastrid.com/sql/sql-joins/

（完）


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

-Advertisement-
Play Games
更多相關文章
  • WSL2-CentOS7固定IP
    ps命令 ps命令來自於英文片語”process status“的縮寫，其功能是用於顯示當前系統的進程狀態。使用ps命令可以查看到進程的所有信息，例如進程的號碼、發起者、系統資源使用占比（處理器與記憶體）、運行狀態等等。幫助我們及時的發現哪些進程出現”僵死“或”不可中斷“等異常情況。 經常會與kill ...
  • 大數據Hadoop之——Cloudera Hadoop（CM 6.3.1+CDH 6.3.2環境部署）
    一、概述 在眾多 Hadoop 版本中， CDH（Cloudera Hadoop） 是 Hadoop 眾多分支中比較出色的版本， 它由Cloudera 發行和維護。CDH 基於 Apache 的 Hadoop 進行重新構建，提供了基於 Web 頁面的群集部署和管理操作。Hadoop發行版除了社區的A ...
  • SpringDataRedis序列化的一些問題
    SpringDataRedis的序列化的一些問題 RedisTemplate可以接收任意Object作為值寫入Redis，但是如果不實現設置序列化器的化預設是採用JDK序列化，序列化後的結果可讀性差並且記憶體占用空間大，如下圖。 自定義RedisTemplate的序列化方式 key和 hashKey採 ...
  • 李陽：京東零售OLAP平臺建設和場景實踐
    導讀： 今天和大家分享京東零售OLAP平臺的建設和場景的實踐，主要包括四大部分： 管控面建設 優化技巧 典型業務 大促備戰 -- 01 管控面建設 1. 管控面介紹 管控面可以提供高可靠高效可持續運維保障、快速部署小時交付的能力，尤其是針對ClickHouse這種運維較弱但是性能很高的OLAP核心引 ...
  • 如何使用 SQL UPDATE 和 DELETE 語句更新或刪除表數據
    本文介紹如何使用 UPDATE 和 DELETE 語句處理表中的數據，還介紹了為什麼 WHERE 子句對 UPDATE 和 DELETE 語句很重要。 一、更新數據 更新（修改）表中的數據，可以使用 UPDATE 語句。有兩種使用 UPDATE 的方式： 更新表中的特定行； 更新表中的所有行。 下麵 ...
  • 如何使用 SQL INSERT 語句將數據插入到表中
    本文介紹如何使用 SQL INSERT 語句將數據插入到表中，如何用 INSERT SELECT 從其他表中導入行，如何用 SELECT INTO 將行導出到一個新表。 一、數據插入 毫無疑問，SELECT 是最常用的 SQL 語句了。但是，還有其他 3 個常用的 SQL 語句需要學習。第一個就是 ...
  • 聊一聊 HBase 是如何寫入數據的？
    i，大家好，我是大D。今天繼續瞭解下 HBase 是如何寫入數據的，然後再講解一下一個比較經典的面試題。 ...
  • 如何利用 SQL UNION 操作符將多條 SELECT 語句組合成一個結果集
    本文介紹如何利用 SQL UNION 操作符將多條 SELECT 語句組合成一個結果集。使用 UNION 可極大地簡化複雜的 WHERE 子句，簡化從多個表中檢索數據的工作。 一、組合查詢 多數 SQL 查詢只包含從一個或多個表中返回數據的單條 SELECT 語句。但是，SQL 也允許執行多個查詢（ ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 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 ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類