詳解MySQL中的多表查詢：多表查詢分類講解、七種JOIN操作的實現

精選專欄鏈接 🔗

  • MySQL技術筆記專欄
  • Redis技術筆記專欄
  • 大模型搭建專欄
  • Python學習筆記專欄
  • 深度學習算法專欄

歡迎訂閱，點贊＋關注，每日精進1%，與百萬開發者共攀技術珠峰

更多內容持續更新中！希望能給大家帶來幫助~ 😀😀😀

分類講解MySQL中的多表查詢

  • 1，什么是多表查詢
  • 2，多表查詢的分類
  • 3，等值連接和非等值連接
    • 3.1，等值連接的定義及應用
    • 3.2，非等值連接的定義及應用
  • 4，自連接和非自連接
    • 4.1，自連接的定義及應用
    • 4.2，非自連接的定義及應用
  • 5，內連接和外連接
    • 5.1，內連接的定義及應用
    • 5.2，外連接的定義及應用
  • 6，使用SQL語言實現七種JOIN操作（面試重點）
    • 6.1，UNION和UNION ALL
    • 6.2，MySQL的7種JOIN操作
      • 6.2.1，內連接
      • 6.2.2，左外連接
      • 6.2.3，右外連接
      • 6.2.4，左排除連接
      • 6.2.5，右排除連接
      • 6.2.6，全外連接
      • 6.2.7，外排除連接

1，什么是多表查詢

多表查詢，也稱為關聯查詢，指兩個或更多個表一起完成查詢操作。

可進行多表查詢的前提條件： 這些一起查詢的表之間是有關系的（一對一、一對多），它們之間一定是有關聯字段，如下圖：員工表和部門表，這兩個表依靠“部門編號”進行關聯，因此符合多表查詢的條件。

在這里插入圖片描述

2，多表查詢的分類

連接查詢通過表之間的關聯條件，將多張表的數據合并輸出。根據匹配邏輯和結果集范圍，可分為以下類型：

  • 等值連接和非等值連接；
  • 自連接和非自連接 ；
  • 內連接和外連接；

接下來我們詳細看一下這些不同種類多表查詢的定義和應用。

3，等值連接和非等值連接

根據多表查詢的連接條件的類型可分為等值連接和非等值連接。

  • 等值連接通過（=）運算符進行比較；
  • 非等值連接通過其他運算符進行比較；

3.1，等值連接的定義及應用

等值連接是最常見的一種連接類型，它基于兩個表之間的相等條件來連接記錄。這通常意味著連接條件中的兩個字段通過等于（=）操作符進行比較。

我們根據前面介紹已知EMPLOTYEES表和DEPARTMENTS表滿足多表查詢的前提條件。當我們有如下需求時：

需求：查詢每一位員工的employee_id和department_name。

注意： 如下圖所示，員工的employee_id位于EMPLOYEES表，而department_name字段位于DEPARTMENTS表。

在這里插入圖片描述
此時正確的SQL語句如下：

SELECT employee_id,department_name
FROM employees,departments# 兩個表的連接條件
WHERE employees.department_id = departments.department_id;

運行結果如下：

在這里插入圖片描述

此即為一個等值連接的應用示例。

3.2，非等值連接的定義及應用

非等值連接則不局限于等于（=）操作符，而是可能使用其他比較操作符（如>、<、>=、<=、<>等），或者通過表達式或函數來連接兩個表。

EMPLOYEES表中每個員工都有SALARY（工資）字段；而JOB_GRADES表中又對不同薪資范圍做了等級的劃分。

在這里插入圖片描述

當我們有如下需求時：

需求：查看員工的姓名、工資、工資等級

SQL語句如下：

SELECT e.last_name, e.salary, j.grade_level
FROM employees e, job_grades j
# 非等值連接條件
WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal

或者：

SELECT e.last_name, e.salary, j.grade_level
FROM employees e, job_grades j
# # 非等值連接條件
WHERE e.salary >= j.lowest_sal AND e.salary<=j.highest_sal

運行結果如下：

在這里插入圖片描述
此即為一個非等值連接的應用示例。

4，自連接和非自連接

根據多表查詢連接的表是否為同一張表可分為自連接和非自連接。（本節之前列舉的多表查詢例子連接的表為不同表，因此均為非自連接）

  • 自連接指連接的表為同一張表；
  • 非自連接連接的表不是同一張表；

4.1，自連接的定義及應用

自連接是指同一張表與其自身進行連接的操作。這種類型的連接通常用于處理具有層級關系的數據，比如在員工表中查找每個員工的直接上級。為了實現這一點，需要為同一個表賦予不同的別名，以便在查詢時區分不同的實例。

自連接對應了表中自我引用的關系。如下圖員工表的例子所示，104號和105號員工的主管是103號員工（103號員工是一名員工，同時擔任主管）。

在這里插入圖片描述

需求： 要查詢員工ID、員工姓名及其管理者ID和姓名

SQL語句如下：

SELECT emp.employee_id,emp.last_name,mgr.employee_id,mgr.last_name# 給同一張表起兩個別名，一份看作員工，一份看作管理者
FROM employees emp ,employees mgr
WHERE emp.`manager_id` = mgr.`employee_id`;

查詢結果如下：

在這里插入圖片描述

此即為一個自連接的示例。

4.2，非自連接的定義及應用

非自連接是最常見的連接形式。非自連接和自連接相反，非自連接指的是不同表之間的連接，用于處理兩個或多個獨立表之間的數據關系。

由于4章節節之前列舉的多表查詢例子連接的表均為不同表，因此均為非自連接。此處不再贅述。

5，內連接和外連接

根據多表查詢連接結果中是否包含未匹配的行可分為內連接和外連接。

  • 連接結果中不包含未匹配行即為內連接；
  • 連接結果中包含未匹配行即為外連接；

5.1，內連接的定義及應用

內連接返回的是滿足連接條件的所有行的交集部分。 這意味著只有當兩個表中存在相應的匹配記錄時，這些記錄才會出現在結果集中。

需求：查詢員工ID及部門名

SQL語句如下：

SELECT employee_id,department_name
FROM employees e,departments d
WHERE e.department_id = d.department_id

或者：

SELECT employee_id,department_name
# INNER JOIN表示內連接（SQL99語法）
FROM employees e INNER JOIN departments d
ON e.`department_id` = d.`department_id`;

查詢結果如下：

在這里插入圖片描述

此即為一個內連接的簡單例子。這段SQL語句的核心在于只把左表和右表中滿足連接條件的數據查出來了，此即為內連接。比如：如果某員工的department_id為空，則不會出現在查詢得到的結果集中。

5.2，外連接的定義及應用

外連接包括主表中的所有記錄，即使它們在另一個表中沒有匹配項。

而外連接又分為左外連接、右外連接和全外連接。

  • 左外連接會返回左表中的所有記錄以及右表中符合條件的記錄；
  • 右外連接會返回右表中的所有記錄以及左表中符合條件的記錄；
  • 全外連接則返回兩張表中的所有記錄，對于沒有匹配項的部分用NULL填充。

需求： 查詢所有的員工姓名、所在部門名信息

注意：提及所有的員工，說明是外連接。

SQL語句如下：

SELECT last_name,department_name
# LEFT OUTER JOIN 表示左外連接 ，以左表employees為基礎
FROM employees e LEFT OUTER JOIN departments d
ON e.`department_id` = d.`department_id`;

或者：

SELECT last_name,department_name
# 省略OUTER，LEFT JOIN 也可表示左外連接 
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`;

運行結果如下：

在這里插入圖片描述

從查詢結果可以看到，結果包含左表中所有的記錄以及右表中符合條件的記錄。即使EMPLOYEES表內存在一個員工的department_name為Null，經過左外連接查詢后依然現實中查詢得到的結果集中。

此即為一個外連接的示例，具體而言是左外連接。接下來我們詳細學習其中JOIN操作。

6，使用SQL語言實現七種JOIN操作（面試重點）

6.1，UNION和UNION ALL

  • 使用UNION操作符可以返回兩個查詢的結果集的并集，去除重復記錄 ；
  • 使用UNION ALL操作符可以返回兩個查詢的結果集的并集，對于兩個結果集的重復部分，不去重；
  • 執行UNION ALL語句時所需要的資源比UNION語句少。 如果明確知道合并數據后的結果數據
    不存在重復數據，或者不需要去除重復的數據，則盡量使用UNION ALL語句，以提高數據查詢的效率。

應用案例：

需求1： 查詢部門編號>90或郵箱包含a的員工信息

實現方式1：

 SELECT * FROM employees WHERE email LIKE '%a%' OR department_id>90;

實現方式2：

SELECT * FROM employees  WHERE email LIKE '%a%'
# union會自動去重
UNION
SELECT * FROM employees  WHERE department_id>90;

需求2：：查詢中國用戶中男性的信息以及美國用戶中年男性的用戶信息

實現方式 ：

SELECT id,cname FROM t_chinamale WHERE csex='男'
UNION ALL
SELECT id,tname FROM t_usmale WHERE tGender='male';

6.2，MySQL的7種JOIN操作

MySQL中共有7種JOIN操作，如下圖所示。但實際上常用的只有四種，它們分別是：

  • 內連接；
  • 左外連接；
  • 右外連接；
  • 全外連接；
    在這里插入圖片描述
    接下來我們一一實現這些JOIN操作。

6.2.1，內連接

內連接返回的是滿足連接條件的所有行的交集部分。 這意味著只有當兩個表中存在相應的匹配記錄時，這些記錄才會出現在結果集中。

內連接圖示如下：

在這里插入圖片描述

需求： 查詢出已分配有效部門的員工的ID和部門名字：

SQL語句如下：

SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`;

6.2.2，左外連接

左外連接會返回左表中的所有記錄以及右表中符合條件的記錄；

左外連接圖示如下 ：

在這里插入圖片描述

需求： 查詢所有員工ID以及部門姓名

SQL語句如下：

SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`;

6.2.3，右外連接

右外連接會返回右表中的所有記錄以及左表中符合條件的記錄；

右外連接圖示如下：

在這里插入圖片描述

需求： 列出所有部門（包括沒有員工的部門），并顯示每個部門中的員工信息（如果有的話）

SQL語句如下：

SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;

6.2.4，左排除連接

左排除連接圖示如下：

在這里插入圖片描述

左排除連接通過在LEFT JOIN的基礎上添加WHERE B.Key IS NULL來實現的，左排除連接返回的是表A中那些在表B中沒有匹配項的記錄。

需求： 查找沒有分配到任何部門的員工的信息

 SELECT employee_id,last_name,department_nameFROM employees e LEFT JOIN departments dON e.`department_id` = d.`department_id`WHERE d.`department_id` IS NULL

運行結果如下：

在這里插入圖片描述

6.2.5，右排除連接

右排除連接用于從右表中選擇那些在左表中沒有匹配記錄的數據行。簡單來說，右排除連接返回的是右表中的所有在左表中找不到匹配項的記錄。

右排除連接圖示如下：

在這里插入圖片描述

需求： 查詢沒有員工關聯的部門信息，即列出那些沒有任何員工分配到的部門。

SQL語句如下：

 SELECT employee_id,last_name,department_nameFROM employees e RIGHT JOIN departments dON e.`department_id` = d.`department_id`WHERE e.`department_id` IS NULL

運行結果如下：

在這里插入圖片描述

6.2.6，全外連接

全外連接則返回兩張表中的所有記錄，對于沒有匹配項的部分用NULL填充。

全外連接圖示如下：

在這里插入圖片描述

觀察示意圖，可以發現：全外連接可以由兩種其它JOIN操作的并集組合而成。具體有兩種組合方式：

  • 方式一：左外連接 UNION ALL 右排除連接；
  • 方式二：右外連接 UNION ALL 左排除連接；

需求： 查詢所有員工（無論是否有對應部門）和所有部門（無論是否有員工）信息。

方式一SQL語句如下：（實際上是合并了兩個SQL語句的查詢結果，通過UNION ALL合并）

SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
UNION ALL
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL;

方式二SQL語句如下：

SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;

注意：

  • 由于MySQL數據庫不識別FULL OUTER JOIN關鍵字（Oracle數據庫支持），全外連接一般通過如上并集的方式等價實現；
  • 使用UNION ALL而不用UNION的原因是UNION ALL無需去重操作， 效率更高；

6.2.7，外排除連接

外排除連接是由左排除連接和右排除連接組合而成。 。它返回左表和右表中沒有與對方表匹配的行，而匹配的行將被排除在結果集之外。

外排除連接圖示如下：

在這里插入圖片描述

觀察示意圖，可以發現：外排除連接是由左排除連接和右排除連接組合而成。

需求： 聯合查詢員工表與部門表之間的不匹配記錄，找出 沒有對應部門的員工以及沒有員工的部門

SQL語句如下：

SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL

本文來自互聯網用戶投稿，該文觀點僅代表作者本人，不代表本站立場。本站僅提供信息存儲空間服務，不擁有所有權，不承擔相關法律責任。
如若轉載，請注明出處：http://www.pswp.cn/bicheng/94027.shtml
繁體地址，請注明出處：http://hk.pswp.cn/bicheng/94027.shtml
英文地址，請注明出處：http://en.pswp.cn/bicheng/94027.shtml

如若內容造成侵權/違法違規/事實不符，請聯系多彩編程網進行投訴反饋email:809451989@qq.com，一經查實，立即刪除！

相關文章

vue3+elemeent-plus, el-tooltip的樣式修改不生效

vue3+elemeent-plus, el-tooltip的樣式修改不生效

修改后的樣式&#xff0c;直接貼圖&#xff0c;經過刪除出現懸浮1、在書寫代碼的時候切記effect“light”&#xff0c;如果你需要的是深色的樣式:disabled"!multiple" 是否禁用<el-tooltip effect"light" placement"top" content"請先選…
閱讀更多...
網頁作品驚艷亮相！這個浪浪山小妖怪網站太治愈了！

網頁作品驚艷亮相！這個浪浪山小妖怪網站太治愈了！

大家好呀&#xff01;今天要給大家分享一個超級治愈的網頁作品——浪浪山小妖怪主題網站&#xff01;這個純原生開發的項目不僅顏值在線&#xff0c;功能也很能打哦&#xff5e;至于靈感來源的話&#xff0c;要從一部動畫說起。最近迷上了治愈系動畫&#xff0c;就想做一個溫暖…
閱讀更多...
搭建最新--若依分布式spring cloudv3.6.6 前后端分離項目--步驟與記錄常見的坑

搭建最新--若依分布式spring cloudv3.6.6 前后端分離項目--步驟與記錄常見的坑

首先 什么拉取代碼&#xff0c;安裝數據庫&#xff0c;安裝redis&#xff0c;安裝jdk這些我就不說了 導入數據庫 &#xff1a;數據庫是分庫表的 &#xff0c;不要建錯了 【一定要注意&#xff0c;不然nacos讀取不到配置文件】這個是給nacos用的這個是給項目配置或項目用的2. 服…
閱讀更多...
分布式唯一 ID 生成方案

分布式唯一 ID 生成方案

在復雜分布式系統中&#xff0c;往往需要對大量的數據和消息進行唯一標識。如在美團點評的金融、支付、餐飲、酒店、貓眼電影等產品的系統中&#xff0c;數據日漸增長&#xff0c;對數據分庫分表后需要有一個唯一 ID 來標識一條數據或消息&#xff0c;數據庫的自增 ID 顯然不能…
閱讀更多...
飛算JavaAI賦能高吞吐服務器模擬：從0到百萬級QPS的“流量洪峰”征服之旅

飛算JavaAI賦能高吞吐服務器模擬：從0到百萬級QPS的“流量洪峰”征服之旅

引言&#xff1a;當“流量洪峰”來襲&#xff0c;如何用低代碼馴服高并發&#xff1f; 在數字化時代&#xff0c;從電商平臺的“雙11”大促到社交網絡的突發熱點事件&#xff0c;再到金融系統的實時交易高峰&#xff0c;服務器時刻面臨著**高吞吐量&#xff08;High Throughput…
閱讀更多...
C#數據訪問幫助類

C#數據訪問幫助類

一.中文注釋using System; using System.Data; using System.Xml; using System.Data.SqlClient; using System.Collections;namespace Microsoft.ApplicationBlocks.Data.Ch {/// <summary>/// SqlServer數據訪問幫助類/// </summary>public sealed class SqlHelp…
閱讀更多...
B站 韓順平 筆記 （Day 21）

B站 韓順平 筆記 （Day 21）

目錄 1&#xff08;面向對象高級部分練習題&#xff09; 1.1&#xff08;題1&#xff09; 1.2&#xff08;題2&#xff09; 1.3&#xff08;題3&#xff09; Vehicles接口類&#xff1a; Horse類&#xff1a; Boat類&#xff1a; Plane類&#xff1a; VehiclesFactory…
閱讀更多...
Linux(十四)——進程管理和計劃任務管理

Linux(十四)——進程管理和計劃任務管理

文章目錄前言一、程序與進程的關系1.1 程序與進程的定義1.2 父進程與子進程二、查看進程信息2.1 ps 命令&#xff08;重點&#xff09;2.2 動態查看進程信息top命令&#xff08;重點&#xff09;2.3 pgrep命令查詢進程信息2.4 pstree命令以樹形結構列出進程信息三、進程的啟動方…
閱讀更多...
太陽光模擬器在無人機老化測試中的應用

太陽光模擬器在無人機老化測試中的應用

在無人機技術飛速發展的當下&#xff0c;其戶外作業環境復雜多變&#xff0c;長期暴露在陽光照射下&#xff0c;部件老化問題日益凸顯&#xff0c;嚴重影響無人機的性能與壽命。紫創測控Luminbox專注于太陽光模擬器技術創新與精密光學測試系統開發&#xff0c;其涵蓋的 LED、鹵…
閱讀更多...
網絡原理-TCP_IP

網絡原理-TCP_IP

1.UDP&#xff08;即用戶數據報協議&#xff09;UDP是一種無連接的傳輸層協議&#xff0c;提供簡單的、不可靠的數據傳輸服務。它不保證數據包的順序、可靠性或重復性&#xff0c;但具有低延遲和高效率的特點。UDP協議段格式16位UDP?度,表?整個數據報(UDP?部UDP數據)的最??…
閱讀更多...
GitHub Actions YAML命令使用指南

GitHub Actions YAML命令使用指南

version: 2 updates:- package-ecosystem: "github-actions"directory: "/"schedule:interval: "weekly"這段代碼是 Dependabot 的配置文件&#xff08;通常放在 .github/dependabot.yml 中&#xff09;&#xff0c;它的作用是 自動化管理 GitHu…
閱讀更多...
決策樹算法學習總結

決策樹算法學習總結

一、經典決策樹算法原理 &#xff08;一&#xff09;ID3 算法 核心思想&#xff1a;以 “信息增益” 作為劃分屬性的選擇標準&#xff0c;通過最大化信息增益來提升數據集的 “純度”。 關鍵概念 —— 信息增益&#xff1a;指某個屬性帶來的 “熵減”&#xff08;即純度提升量&…
閱讀更多...
內網安全——出網協議端口探測

內網安全——出網協議端口探測

在實戰中難免會遇到各種各樣的情況&#xff0c;其中對于目標主機是否出網這是一個十分值得收集的信息&#xff0c;因為完全不出網你就獲取不到主機了 端口 Linux 系統 對于 Linux 系統&#xff0c;探測其允許出網的端口&#xff0c;這里使用的是 Linux 的自帶命令&#xff0c;所…
閱讀更多...
C#WPF實戰出真汁13--【營業查詢】

C#WPF實戰出真汁13--【營業查詢】

1、營業查詢介紹本模塊是最后一個模塊&#xff0c;該板塊需要的功能有&#xff1a;營業數據列表&#xff0c;查詢數據&#xff0c;導出數據&#xff0c;數據統計。2、UI設計布局TabControl 是 WPF 中用于創建多頁標簽式界面的控件&#xff0c;常用于組織多個子內容區域。每個子…
閱讀更多...
基于 Java 和 MySQL 的精品課程網站

基于 Java 和 MySQL 的精品課程網站

基于 Java 和 MySQL 的精品課程網站設計與實現一、 畢業設計&#xff08;論文&#xff09;任務書摘要&#xff1a;近年來&#xff0c;教育信息化發展十分迅猛&#xff0c;人們的教育觀念、教育手段、學習方法、學習渠道等等都發生了重大的變化。知識性人才也已經日益成為了一個…
閱讀更多...
全球首款 8K 全景無人機影翎 A1 發布解讀：航拍進入“先飛行后取景”時代

全球首款 8K 全景無人機影翎 A1 發布解讀：航拍進入“先飛行后取景”時代

全球首款 8K 全景無人機影翎 A1 發布解讀&#xff1a;航拍進入“先飛行后取景”時代 特別說明&#xff1a;本文所有圖片素材來源于影翎官網 影翎官方介紹稱&#xff1a;“全球首款”是指截至 2025 年&#xff0c;A1 是首臺全面整合的全景無人機&#xff1a;無需外掛全景相機配件…
閱讀更多...
androidstudio內存大小配置

androidstudio內存大小配置

help->Edit Custom Vm option-Xmx8096m或者其他數值 改成-Xmx10240m然后設置里面的內存大小也要修改一下
閱讀更多...
vue3和elementPlus中的el-dropdown-menu中的背景樣式修改

vue3和elementPlus中的el-dropdown-menu中的背景樣式修改

1. 效果展示2. 代碼展示在el-dropdown-menu下加載類名,class"my-dropdown-menu"<el-dropdown-menu class"my-dropdown-menu"><el-dropdown-item :command"{ action: upgrade, data }">升級</el-dropdown-item><el-dropdown…
閱讀更多...
計算機網絡--HTTP協議

計算機網絡--HTTP協議

1. 什么是 HTTP 協議全稱&#xff1a;Hyper Text Transfer Protocol&#xff08;超文本傳輸協議&#xff09;作用&#xff1a;用于在服務器與客戶端&#xff08;通常是瀏覽器&#xff09;之間傳輸超文本數據&#xff08;如文字、圖片、視頻、音頻&#xff09;的應用層協議。工作…
閱讀更多...
Bee1.17.25更新Bug,完善功能.不支持NOSQL,分庫分表Sharding(2.X版有)

Bee1.17.25更新Bug,完善功能.不支持NOSQL,分庫分表Sharding(2.X版有)

Bee 1.17.25 正常的ORM功能都有,但不支持NOSQL, 分庫分表Sharding; 若需要可使用2.X版. Bee, 接口簡單&#xff0c;功能齊全&#xff0c;性能好&#xff0c;支持原生分頁性能更高&#xff1b;還有分庫分表 (Sharding 分片) 功能&#xff0c;也支持 MongoDB ORM. Bee Hiberna…
閱讀更多...
最新文章

Copyright @ 2022~2023