SELECT ...
UNION [ALL | DISTINCT]
SELECT ...
[UNION [ALL | DISTINCT] SELECT ...]
UNION = UNION DISTINCT:結果集去重(隱式排序+唯一化),較慢UNION ALL:直接合併,**不去重、不排序**,效能高得多需用括號包裹各子查詢,ORDER BY/LIMIT 放最後:
(SELECT a FROM t WHERE a=10)
UNION
(SELECT a FROM t WHERE a=11)
ORDER BY a LIMIT 10;
(SELECT * FROM `blog` WHERE top=1 ORDER BY created DESC)
UNION
(SELECT * FROM `blog` WHERE top=0 ORDER BY created DESC)
LIMIT 2, 3;
優雅實現「置頂優先、其次按時間」的單查詢分頁。
SELECT * FROM (
SELECT * FROM t1 ORDER BY score DESC
UNION ALL
SELECT * FROM t2 ORDER BY score DESC
) AS combined
ORDER BY score DESC;
-- 正確:ORDER BY 別名
(SELECT a AS b FROM t) UNION (SELECT ...) ORDER BY b;
-- 錯誤:ORDER BY 原列名
(SELECT a AS b FROM t) UNION (SELECT ...) ORDER BY a; -- Unknown column 'a'
(SELECT a FROM t WHERE a=10 ORDER BY a LIMIT 10)
UNION
(SELECT a FROM t WHERE a=11 ORDER BY a LIMIT 10);
SELECT *, 1 AS ord FROM women WHERE status=2 AND (title LIKE '%2014%' OR title LIKE '%秋冬%')
UNION
SELECT *, 2 AS ord FROM women WHERE status=2 AND (keywords LIKE '%2014%' OR keywords LIKE '%秋冬%')
ORDER BY ord, id DESC LIMIT 0,15;
用常數列 ord 標記來源優先級,再二次排序。
WHERE ... OR ... 或應用層合併