SQL多表查詢比較與關聯資料列辨別

– 8 分鐘閱讀

在資料函式庫開發中，經常需要比較不同資料表之間的資料，例如找出一個表中存在但另一個表中不存在的記錄，或者確認兩個表是否包含相同的資料。這些操作通常需要結合多種 SQL 技術，例如集合運算、子查詢和連線。理解這些技術的應用場景和使用方法，對於提升資料函式庫查詢效率至關重要。常見的技術包含使用 EXCEPT 或 MINUS 運算子來比較兩個結果集的差異，但並非所有資料函式庫系統都支援這些運算子。在 MySQL 中，可以使用 NOT IN 或 NOT EXISTS 來實作類別似的功能，但需要注意 NULL 值對 NOT IN 的影響。使用 NOT EXISTS 通常是更穩健的選擇，因為它不受 NULL 值的影響。此外，外連線也是一種有效的方法，可以保留所有主要表格的資料，並找出與次要表格無關聯的記錄。

從一個表中檢索另一個表中不存在的值

在處理多表查詢時，經常需要找出一個表中存在而另一個表中不存在的記錄。本文將探討不同資料函式庫系統中如何實作這一功能，並重點分析 NULL 值對查詢結果的影響。

使用 EXCEPT 或 MINUS 運算子

在支援 EXCEPT 或 MINUS 運算子的資料函式庫系統（如 PostgreSQL、Oracle）中，可以使用這些運算子來找出兩個查詢結果之間的差異。

PostgreSQL 與 Oracle 的解決方案

-- PostgreSQL 使用 EXCEPT
SELECT deptno FROM dept
EXCEPT
SELECT deptno FROM emp;

-- Oracle 使用 MINUS
SELECT deptno FROM dept
MINUS
SELECT deptno FROM emp;

這兩種寫法的作用相同，都是傳回 dept 表中存在但 emp 表中不存在的 deptno。

內容解密：

  • EXCEPT 和 MINUS 運算子用於傳回第一個查詢結果中存在但第二個查詢結果中不存在的行。
  • 這兩個運算子自動去除重複行，確保每個 deptno 只被傳回一次。
  • 在 Oracle 中，使用 MINUS 代替 EXCEPT。

MySQL 的解決方案

MySQL 不支援 EXCEPT 或 MINUS 運算子，但可以使用 NOT IN 或 NOT EXISTS 來實作相同的功能。

-- 使用 NOT IN
SELECT DISTINCT deptno
FROM dept
WHERE deptno NOT IN (SELECT deptno FROM emp);

內容解密：

  • NOT IN 用於找出 dept 表中 deptno 不在 emp 表 deptno 列表中的記錄。
  • 使用 DISTINCT 關鍵字確保每個 deptno 只被傳回一次。
  • 需要注意的是，如果子查詢結果中包含 NULL 值，NOT IN 的行為可能會與預期不同。

處理 NULL 值

當子查詢結果中包含 NULL 值時，NOT IN 的行為可能會導致查詢不傳回任何行。這是因為在 SQL 中，FALSE OR NULL 的結果是 NULL，而 NULL 被視為 FALSE。

-- 建立測試表 new_dept
CREATE TABLE new_dept(deptno INTEGER);
INSERT INTO new_dept VALUES (10);
INSERT INTO new_dept VALUES (50);
INSERT INTO new_dept VALUES (NULL);

-- 使用 NOT IN 查詢
SELECT *
FROM dept
WHERE deptno NOT IN (SELECT deptno FROM new_dept);

在上述例子中，由於 new_dept 表中包含 NULL 值，查詢結果為空。

內容解密：

  • 當子查詢結果包含 NULL 值時，NOT IN 可能不會傳回預期的結果。
  • 這是因為 NOT IN 本質上是一個 OR 操作，而在 SQL 中，涉及 NULL 的 OR 操作可能傳回 NULL 或 FALSE。

使用 NOT EXISTS 替代 NOT IN

為了避免 NULL 值帶來的問題，可以使用 NOT EXISTS 代替 NOT IN。

-- 使用 NOT EXISTS
SELECT d.deptno
FROM dept d
WHERE NOT EXISTS (
    SELECT 1
    FROM emp e
    WHERE d.deptno = e.deptno
);

內容解密：

  • NOT EXISTS 用於檢查子查詢是否傳回任何行。
  • 如果子查詢沒有傳回任何行，則 NOT EXISTS 傳回 TRUE，否則傳回 FALSE。
  • 這種方法不受 NULL 值的影響，能夠正確傳回預期的結果。

在多表查詢中找出無關聯的資料列

在進行多表查詢時，我們經常會遇到需要找出在一張表中存在但在另一張表中沒有對應資料的情況。例如，找出沒有員工的部門。這個問題看似簡單，但實際處理時需要謹慎選擇合適的查詢方法。

使用 NOT EXISTS 子查詢找出無關聯資料

問題描述

假設我們需要找出 DEPT 表中那些在 EMP 表中沒有對應員工的部門。

解決方案

使用相關子查詢（Correlated Subquery）結合 NOT EXISTS 來找出這些部門。

SELECT *
FROM dept d
WHERE NOT EXISTS (
  SELECT NULL
  FROM emp e
  WHERE d.deptno = e.deptno
)

詳細解析：

  1. 外層查詢逐一檢查 DEPT 表中的每一列。
  2. 對於每一列，外層查詢執行內層子查詢，檢查 EMP 表中是否存在與當前部門編號 (DEPTNO) 相匹配的資料。
  3. 如果子查詢傳回結果，則 EXISTS 為 TRUE，而 NOT EXISTS 為 FALSE，該部門資料被丟棄。
  4. 如果子查詢沒有傳回結果，則 NOT EXISTS 為 TRUE，該部門資料被保留，因為它代表一個在 EMP 表中沒有員工的部門。
  5. 子查詢中的 SELECT 清單內容並不重要，因為我們只關心是否存在匹配的資料列。

使用外連線（Outer Join）找出無關聯資料

問題描述

同樣是找出沒有員工的部門，但這次我們希望獲得更多 DEPT 表中的欄位資訊。

解決方案

使用左外連線（Left Outer Join）並篩選出那些在 EMP 表中沒有匹配資料的列。

SELECT d.*
FROM dept d
LEFT OUTER JOIN emp e ON d.deptno = e.deptno
WHERE e.deptno IS NULL

詳細解析：

  1. 首先對 DEPT 和 EMP 表進行左外連線，確保 DEPT 表中的每一列都被包含在結果集中。
  2. 連線條件是 DEPTNO 欄位相等。
  3. 結果集中，那些在 EMP 表中沒有匹配資料的列，其 EMP 相關欄位將為 NULL。
  4. 透過 WHERE 子句篩選出 EMP.DEPTNO IS NULL 的列，即代表那些沒有員工的部門。

在不幹擾其他連線的情況下新增連線

問題描述

假設我們有一個查詢能夠傳回員工及其部門位置，但現在我們還想新增員工獲得獎金的日期。然而，並非所有員工都有獎金，直接連線 EMP_BONUS 表會導致部分沒有獎金的員薪水料被遺漏。

解決方案

使用左外連線來新增與 EMP_BONUS 表的連線，以保留所有員薪水料。

SELECT e.ename, d.loc, eb.received
FROM emp e
JOIN dept d ON e.deptno = d.deptno
LEFT JOIN emp_bonus eb ON e.empno = eb.empno
ORDER BY 2

詳細解析：

  1. 首先對 EMP 和 DEPT 表進行內連線，以獲得員工及其部門位置。
  2. 然後對結果集與 EMP_BONUS 表進行左外連線，以獲得員工的獎金日期（如果有的話）。
  3. 使用 ORDER BY 子句對結果按部門位置排序。

或者，也可以使用純量子查詢（Scalar Subquery）來模擬外連線的效果：

SELECT e.ename, d.loc,
       (SELECT eb.received FROM emp_bonus eb WHERE eb.empno = e.empno) AS received
FROM emp e, dept d
WHERE e.deptno = d.deptno
ORDER BY 2

詳細解析：

  1. 對 EMP 和 DEPT 表進行內連線，以獲得員工及其部門位置。
  2. 使用純量子查詢從 EMP_BONUS 表中取得員工的獎金日期。
  3. 如果員工沒有獎金記錄，子查詢將傳回 NULL。

這兩種方法都能有效地在原有查詢結果的基礎上新增所需的資訊，而不會丟失原有的資料。

比較兩個資料表或檢視是否具有相同的資料

問題描述

在資料函式倉管理中，經常需要比較兩個資料表或檢視是否包含相同的資料。這包括檢查資料的基數（行數）和具體的值。本篇文章將探討如何使用SQL查詢來判斷兩個資料表或檢視是否具有相同的資料。

解決方案

使用集合運算子（DB2 和 PostgreSQL）

對於支援EXCEPT和UNION ALL集合運算的資料函式庫系統，如DB2和PostgreSQL，可以使用以下查詢：

(
  SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno,
         COUNT(*) AS cnt
  FROM V
  GROUP BY empno, ename, job, mgr, hiredate, sal, comm, deptno
  EXCEPT
  SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno,
         COUNT(*) AS cnt
  FROM emp
  GROUP BY empno, ename, job, mgr, hiredate, sal, comm, deptno
)
UNION ALL
(
  SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno,
         COUNT(*) AS cnt
  FROM emp
  GROUP BY empno, ename, job, mgr, hiredate, sal, comm, deptno
  EXCEPT
  SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno,
         COUNT(*) AS cnt
  FROM V
  GROUP BY empno, ename, job, mgr, hiredate, sal, comm, deptno
)

使用集合運算子（Oracle）

對於Oracle，可以使用MINUS運算子來達到相同的效果：

(
  SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno,
         COUNT(*) AS cnt
  FROM V
  GROUP BY empno, ename, job, mgr, hiredate, sal, comm, deptno
  MINUS
  SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno,
         COUNT(*) AS cnt
  FROM emp
  GROUP BY empno, ename, job, mgr, hiredate, sal, comm, deptno
)
UNION ALL
(
  SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno,
         COUNT(*) AS cnt
  FROM emp
  GROUP BY empno, ename, job, mgr, hiredate, sal, comm, deptno
  MINUS
  SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno,
         COUNT(*) AS cnt
  FROM V
  GROUP BY empno, ename,job,mgr,hiredate,sal.comm.deptno
)

相關子查詢（MySQL 和 SQL Server）

對於不支援上述集合運算的資料函式庫系統，如MySQL和SQL Server，可以使用相關子查詢來實作：

SELECT *
FROM (
  SELECT e.empno,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm,e.deptno,
         COUNT(*) AS cnt
  FROM emp e
  GROUP BY empno.ename,job,mgr,hiredate,sal.comm.deptno 
) e 
WHERE NOT EXISTS (
    SELECT NULL 
    FROM ( 
      SELECT v.emp,v.ename,v.job,v.mgr,v.hiredate,v.sal.v.comm,v.deptno 
             COUNT(*) as cnt 
      FROM V 
      GROUP BY emp.ename.job,mgr.hiredate.sal.comm.dept 
    ) v 
    WHERE v.emp = e.emp AND 
          v.ename = e.ename AND 
          v.job = e.job AND 
          COALESCE(v.mgr) = COALESCE(e.mgr) AND 
          v.hiredate = e.hire 
          v.sal = e.sal AND 
          v.dept = e.dept AND 
          v.cnt = e.cnt AND 
          COALESCE(v.com) = COALESCE(e.com)
) 
UNION ALL 
SELECT *
FROM (
    SELECT v.emp,v.ename,v.job,v.mgr.v.hiredate,v.sal,v.comm,v,depto,
           COUNT(*) as cnt 
    FROM V 
    GROUP BY emp.ename,job,mgr,hiredate,sal.comm.depto 
) v 
WHERE NOT EXISTS ( 
    SELECT NULL 
    FROM ( 
      SELECT e.emp,e.ename,e.job,e.mgr,e.hiredate,e.sal.e.comm,e.depto,
             COUNT(*) as cnt 
      FROM emp e 
      GROUP BY empename,job,mgr,hiredate,salcommdepto 
    ) e 
    WHERE v.emp = e.emp AND 
          v.ename = e.ename AND 
          v.job = e.job AND  
          COALESCE(v.mgr) = COALESCE(e.mgr) AND  
          v.hiredate = e.hire AND  
          v.sal = e.sal AND  
          v.depto = e.depto AND  
          v.cnt = e.cnt AND  
          COALESCE(v.com) = COALESCE(e.com)
)

#### 程式碼解析：

  • 第一段程式碼（DB2 和 PostgreSQL）：利用EXCEPT運算子找出在檢視V中存在但在表emp中不存在的記錄，以及相反的情況。然後使用UNION ALL合併這兩部分結果。
  • 第二段程式碼（Oracle）：與第一段類別似，但使用MINUS運算子替代EXCEPT。
  • 第三段程式碼（MySQL 和 SQL Server）：使用相關子查詢和NOT EXISTS子句來找出在兩個表中存在差異的記錄。

辨別兩個表格是否具有相同資料

在資料函式倉管理系統（DBMS）中，確認兩個表格是否包含相同的資料是一個常見的需求。本篇文章將探討如何使用SQL查詢來比較兩個表格的資料是否相同。

使用 UNION 運算子進行簡單比較

以下是一個簡單的查詢範例，用於檢查兩個表格的資料筆數是否相同：

select count(*)
from emp
union
select count(*)
from dept

內容解密：

  • 這個查詢使用了 UNION 運算子來合併兩個 SELECT COUNT(*) 查詢的結果。
  • 如果兩個表格的資料筆數相同，UNION 將只傳回一個結果，因為它會過濾重複的行。
  • 在這個範例中，由於傳回了兩個不同的計數（4 和 14），我們可以確定兩個表格的資料筆數不相同。

使用 EXCEPT 或 MINUS 運算子進行詳細比較

對於DB2、Oracle和PostgreSQL，可以使用 EXCEPT 運算子來找出兩個表格之間的差異。

(
  select empno, ename, job, mgr, hiredate, sal, comm, deptno, count(*) as cnt
  from V
  group by empno, ename, job, mgr, hiredate, sal, comm, deptno
  except
  select empno, ename, job, mgr, hiredate, sal, comm, deptno, count(*) as cnt
  from emp
  group by empno, ename, job, mgr, hiredate, sal, comm, deptno
)

內容解密：

  • 這個查詢比較了檢視 V 和表格 emp 中的資料。
  • 使用 EXCEPT 運算子找出在 V 中存在但在 emp 中不存在，或者計數不同的資料列。
  • 結果顯示了員工 WARD 的重複資料列，表明 V 中 WARD 的資料筆數與 emp 中的不同。

使用 NOT EXISTS 進行比較（適用於 MySQL 和 SQL Server）

對於 MySQL 和 SQL Server，可以使用 NOT EXISTS 子查詢來比較兩個表格。

select *
from (
  select e.empno, e.ename, e.job, e.mgr, e.hiredate, e.sal, e.comm, e.deptno, count(*) as cnt
  from emp e
  group by empno, ename, job, mgr, hiredate, sal, comm, deptno
) e
where not exists (
  select null
  from (
    select v.empno, v.ename, v.job, v.mgr, v.hiredate, v.sal, v.comm, v.deptno, count(*) as cnt
    from v
    group by empno, ename, job, mgr, hiredate, sal, comm, deptno
  ) v
  where v.empno = e.empno
  and v.ename = e.ename
  and v.job = e.job
  and v.mgr = e.mgr
  and v.hiredate = e.hiredate
  and v.sal = e.sal
  and v.deptno = e.deptno
  and v.cnt = e.cnt
  and coalesce(v.comm,0) = coalesce(e.comm,0)
)

內容解密：

  • 這個查詢使用子查詢和 NOT EXISTS 運算子來找出在 emp 中存在但在檢視 V 中不存在的資料列。
  • 它比較了每個員工的詳細資料和計數，以確保兩邊的資料完全匹配。
玄貓 BlackCat

玄貓 BlackCat

技術愛好者，專注於分享程式開發、雲端技術與 AI 應用的心得體會。