MySQL SELECT 進階查詢技巧全解析：別名、去重、NULL 處理與多表連接

前言

SELECT 語句是 MySQL 中最核心、使用頻率最高的命令。除了基本的數據檢索，它還提供了許多進階功能，能夠極大地提升查詢效率與靈活性。本文將系統梳理列/表別名、DISTINCT 去重、NULL 值判斷、大小寫敏感性規則，以及跨表連接查詢（全連接與左連接）的實戰用法，助你掌握 SELECT 的精髓。

一、列與表的別名：讓查詢更清晰

1.1 列別名的三種引用方式

在 ORDER BY 和 GROUP BY 子句中，可使用「列名」、「列別名」或「列位置（從 1 開始）」來引用輸出列。例如從 pet 表查詢寵物名與種類：

-- 直接用列名
SELECT name, species FROM pet ORDER BY name, species;

-- 用列位置（1 代表 name，2 代表 species）
SELECT name, species FROM pet ORDER BY 1, 2;

-- 定義別名後引用別名
SELECT name AS n, species AS s FROM pet ORDER BY n, s;

三種寫法輸出結果順序一致，但使用別名時，結果集的列標題會變為 n、s，這在 CREATE TABLE ... SELECT 建表時特別有用：

CREATE TABLE pet1
SELECT name AS animal, owner AS child FROM pet;
-- 新表欄位直接命名為 animal、child，無需再 ALTER TABLE

1.2 別名在 GROUP BY / HAVING 中的應用

別名可用於 GROUP BY、ORDER BY、HAVING 子句，但 不可用於 WHERE 子句（因執行順序 WHERE 早於別名解析）。錯誤示例：

-- 錯誤：WHERE 不識別 total 別名
SELECT id, COUNT(*) AS total FROM pet WHERE total > 1 GROUP BY species;
-- ERROR 1054: Unknown column 'total' in 'where clause'

正確寫法改用 HAVING：

SELECT species, COUNT(*) AS total FROM pet
GROUP BY species HAVING total > 1;

1.3 表別名與自連接

表別名語法類似列別名，常用於「自連接」（自我關聯查詢）。例如為寵物尋找同品種的異性配對：

SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species
FROM pet AS p1, pet AS p2
WHERE p1.species = p2.species
  AND p1.sex = 'f' AND p2.sex = 'm';

別名讓同一張表的不同實例清晰區分，避免列名衝突。

二、DISTINCT：剔除重複記錄

當結果集出現完全相同的行時，DISTINCT 關鍵字可保證輸出唯一行。注意：DISTINCT 作用於 整行所有選定列的組合，而非單一列。

-- 查詢 Benny 飼養的寵物（含重複品種/性別）
SELECT name, species, sex FROM pet WHERE owner = 'Benny';
-- 結果：Fang/dog/m 、Slim/snake/m（兩行不同）

-- 加上 DISTINCT 後仍為兩行，因 name 不同
SELECT DISTINCT name, species, sex FROM pet WHERE owner = 'Benny';

-- 僅選 owner、sex 時，去重後僅剩一行
SELECT DISTINCT owner, sex FROM pet WHERE owner = 'Benny';
-- 結果：Benny/m

若表中存在完全重複行，SELECT DISTINCT * FROM tbl_name 可移除冗餘輸出。

三、NULL 值的特殊處理

3.1 正確的 NULL 判斷

NULL 代表「無值」或「未知」，不能使用 =、!=、<、> 等算術比較運算符，任何與 NULL 的比較結果皆為 NULL（即 FALSE）。

SELECT 1 = NULL, 1 != NULL, 1 < NULL, 1 > NULL;
-- 全部返回 NULL

-- 必須用 IS NULL / IS NOT NULL
SELECT 1 IS NULL, 1 IS NOT NULL;  -- 0, 1

3.2 實戰陷阱與最佳實踐

• WHERE 子句：WHERE death IS NOT NULL 查找已故寵物；WHERE phone = NULL 永無結果，須改 WHERE phone IS NULL。
• 空字串 vs NULL：INSERT ... VALUES (NULL) 表示「號碼未知」；VALUES ('') 表示「無電話」。兩者語意截然不同。
• 運算與函數：包含 NULL 的表達式通常返回 NULL（如 1+NULL、CONCAT('str', NULL)），可用 IFNULL() 處理。
• 排序與分組：ORDER BY 升序時 NULL 排最前，降序排最後；GROUP BY 視所有 NULL 為相等。
• 索引限制：MySQL 早期版本不允許可為 NULL 的列建立索引，需聲明 NOT NULL。

四、大小寫敏感性規則速查表

五、多表連接查詢：全連接 vs 左連接

5.1 全連接（笛卡爾積）

SELECT * FROM first, last 產生兩表行數相乘的組合（3×3=9 行）。若無 WHERE 過濾，大表連接極易產生百萬級結果集，導致效能災難。實務上必須加入連接條件：

SELECT * FROM first, last WHERE first.id = last.id;
-- 僅返回匹配行（內連接效果）

5.2 左連接（LEFT JOIN）

語法：SELECT ... FROM t1 LEFT JOIN t2 ON 條件。強制保留左表所有行，右表無匹配時以 NULL 填充。

SELECT * FROM first LEFT JOIN last ON first.id = last.id;
-- first.id=1 在 last 無匹配，右側列顯示 NULL

5.3 LEFT JOIN 進階技巧

• 尋找左表獨有行：WHERE right_table.id IS NULL（右表無匹配）。
• 等價於內連接：LEFT JOIN ... ON ... WHERE right_table.id IS NOT NULL。
• ON 條件 vs WHERE 條件：ON 決定如何匹配；WHERE 過濾最終結果集。混淆兩者會導致語意錯誤。

六、總結

本文涵蓋的 SELECT 進階技巧是日常開發的高頻工具：

1. 善用別名簡化引用、美化輸出、支援自連接；
2. DISTINCT 精準去重，注意作用於整行；
3. NULL 操作務必用 IS NULL / IS NOT NULL，區分空字串；
4. 掌握大小寫規則，避免跨平台遷移踩坑；
5. 連接查詢優先考慮 LEFT JOIN 語意清晰，大表嚴禁無條件全連接。

熟練運用這些特性，能讓 SQL 更簡潔、效能更穩健、維護更容易。