SQL 多表聯合查詢的幾種方式詳解

2023-02-16 06:02:30

前言

連線查詢是關係資料中最主要的查詢，包括內連線、外連線等。通過連線運運算元可以實現多個表查詢。

正文

連線查詢主要分為三種：內連線、外連線、交叉連線。

內連線

使用比較運運算元（包括=、>、<、<>、>=、<=、!> 和!<）進行表間的比較操作，查詢與連線條件相匹配的資料。根據所使用的比較方式不同，內連線分為等值連線、自然連線和自連線三種。

關鍵字 INNER JOIN

等值連線/相等連線

使用”=”關係將表連線起來的查詢，其查詢結果中列出被連線表中的所有列，包括其中的重複列

SELECT
    PM_ACT_JOB_RLS.*, PM_ACT_RLS.*
FROM
    PM_ACT_JOB_RLS
INNER JOIN PM_ACT_RLS ON PM_ACT_JOB_RLS.RlsPK = PM_ACT_RLS.RlsPK

自然連線

等值連線中去掉重複的列，形成的連線

SELECT
    PM_ACT_JOB_RLS.JobPK,
    PM_ACT_RLS.RlsPK,
    RlsName
FROM
    PM_ACT_JOB_RLS
INNER JOIN PM_ACT_RLS ON PM_ACT_JOB_RLS.RlsPK = PM_ACT_RLS.RlsPK

自連線

如果在一個連線查詢中，設計到的兩個表都是同一個表，這種查詢稱為自連線查詢。

--c1、c2邏輯上是兩張表，物理上是一張表
SELECT
    c1.CategoryID,
    c1.CategoryName
FROM
    [dbo].[Category] c1
INNER JOIN [dbo].[Category] c2 ON c1.[CategoryID] = c2.[ParentID]

外連線

內連線只返回滿足連線條件的資料行，外連線不只列出與連線條件相匹配的行，而是列出左表（左外連線時）、右表（右外連線時）或兩個表（全外連線時）中所有符合搜尋條件的資料行。外連線分為左外連線、右外連線、全外連線三種。

左外連線

返回左表中的所有行，如果左表中行在右表中沒有匹配行，則在相關聯的結果集中右表的所選擇欄位均為NULL。

關鍵字 LEFT [OUTER] JOIN

--左外連線  --1679條資料
SELECT
    me.*,
    mo.*
FROM
    MM_LOTS_EXT AS me
LEFT OUTER JOIN MM_LOT_OPERATIONS AS mo ON me.LotID = mo.SerialNumber

注意：

left join中關於where和on條件的幾個知識點：

多表left join是會生成一張臨時表，並返回給使用者
where條件是針對最後生成的這張臨時表進行過濾，過濾掉不符合where條件的記錄
on條件是對left join的右表進行條件過濾，但依然返回左表的所有行，右表中沒有的補為NULL
on條件中如果有對左表的限制條件，無論條件真假，依然返回左表的所有行,但是會影響右表的匹配值。也就是說on中左表的限制條件隻影響右表的匹配內容，不影響返回行數。

範例：

SELECT g.*,f.* FROM groups g LEFT JOIN factor f ON f.groupId = g.id and f.isDel=0 where g.isDel=0

結論：

1.對左表新增條件限制需在where條件中新增，不能放到on後面
2.對右表新增條件限制需放到on後面，在where條件中新增會有資料行數差異出現問題。

右外連線

返回右表中的所有行，如果右表中行在左表中沒有匹配行，則在左表中相關欄位返回NULL值。

關鍵字 RIGHT [OUTER] JOIN

--右外連線  --209條資料
SELECT
    me.*,
    mo.*
FROM
    MM_LOTS_EXT AS me
RIGHT OUTER JOIN MM_LOT_OPERATIONS AS mo ON me.LotID = mo.SerialNumber

全外連線/完全外連線

返回兩個連線中所有的記錄資料，是左外連線和右外連線的並集。

關鍵字 FULL [OUTER] JOIN

--全外連線  --1816條資料 
SELECT
    me.*,
    mo.*
FROM
    MM_LOTS_EXT AS me
FULL OUTER JOIN MM_LOT_OPERATIONS AS mo ON me.LotID = mo.SerialNumber

交叉連線/笛卡爾積

兩個表做笛卡爾積，得到的結果集的行數是兩個表的行數的乘積。

關鍵字 CROSS JOIN

--笛卡爾積 (cross join後加條件只能用where,不能用on)  --45條資料（9*5）
SELECT
    *
FROM
    PM_ACT_JOB_RLS
CROSS JOIN PM_ACT_RLS

注意：帶有where條件的子句，往往會先生成兩個錶行數乘積的資料表，然後從根據where條件從中選擇。

當資料量比價大的時候，笛卡爾積操作會很消耗資料庫的效能

總結

連線查詢是關係型資料庫中的一個重要標誌，這次和大家一起復習多表查詢的幾種操作，希望給您帶來幫助！

到此這篇關於SQL 多表聯合查詢的幾種方式的文章就介紹到這了,更多相關SQL 多表聯合查詢內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！