SQL 實戰練習：查詢馬德里客戶及訂單數（含 LEFT JOIN 避坑指南）

題目

查詢來自馬德里且訂單數少於 3 的客戶（包含零訂單客戶）。

建表腳本

CREATE TABLE dbo.Customers (
    CustomerId CHAR(5) PRIMARY KEY,
    City VARCHAR(10) NOT NULL
);
INSERT INTO dbo.Customers VALUES
('FISSA','Madrid'),('FRNDO','Madrid'),('KRLOS','Madrid'),('MRPHS','Zion');

CREATE TABLE dbo.Orders (
    OrderId INT PRIMARY KEY,
    CustomerId CHAR(5) NULL REFERENCES Customers(CustomerId)
);
INSERT INTO dbo.Orders VALUES
(1,'FRNDO'),(2,'FRNDO'),(3,'KRLOS'),(4,'KRLOS'),(5,'KRLOS'),(6,'MRPHS'),(7,NULL);

錯誤嘗試 1：內連接 + 子查詢（漏掉零訂單客戶）

SELECT CustomerId, COUNT(*) AS OrderCount
FROM dbo.Orders
WHERE CustomerId IN (SELECT CustomerId FROM dbo.Customers WHERE City = 'Madrid')
GROUP BY CustomerId
HAVING COUNT(*) < 3;

結果：缺少 FISSA（0 筆訂單，不在 Orders 表中）。

錯誤嘗試 2：LEFT JOIN 但 HAVING 用錯列

SELECT C.CustomerId, COUNT(O.CustomerId) AS OrderCount
FROM dbo.Customers C
LEFT JOIN dbo.Orders O ON C.CustomerId = O.CustomerId
WHERE C.City = 'Madrid'
GROUP BY C.CustomerId
HAVING COUNT(C.CustomerId) < 3;  -- 錯誤：統計 Customers 行數恒為 1

正確寫法：LEFT JOIN + 統計 Orders 主鍵

SELECT 
    C.CustomerId AS 客戶編號,
    COUNT(O.OrderId) AS 訂單數
FROM dbo.Customers AS C
LEFT JOIN dbo.Orders AS O ON C.CustomerId = O.CustomerId
WHERE C.City = 'Madrid'
GROUP BY C.CustomerId
HAVING COUNT(O.OrderId) < 3
ORDER BY 訂單數;  -- 標準答案額外加排序

關鍵點總結

LEFT JOIN 執行邏輯圖解

Customers LEFT JOIN Orders 產生中間結果：

CustomerId | OrderId
FISSA      | NULL
FRNDO      | 1
FRNDO      | 2
KRLOS      | 3
KRLOS      | 4
KRLOS      | 5
MRPHS      | 6
NULL       | 7  -- 此行被 WHERE City='Madrid' 過濾掉

分組聚合後：FISSA=0, FRNDO=2, KRLOS=3 → HAVING < 3 保留 FISSA、FRNDO。