MySQL 進階查詢大全：子查詢、連接查詢、聯合查詢實戰範例

範例資料準備

兩張表：

• user：user_id, username, age, sex
• toy：user_id, toyname

基本查詢關鍵字

AS, SUM, DESC, GROUP BY, ORDER BY, AVG, MIN, MAX, COUNT, LIMIT

常用範例

-- 別名、排序、限制筆數
select * from user as u order by age limit 5;

-- 分組聚合
select sum(age) from user group by sex;

-- 計數
select count(user_id) from user;

-- 條件查詢
select * from user where username='chen';
select username from user where age=18 order by user_id DESC;

子查詢

關鍵字：IN, NOT IN, EXISTS, NOT EXISTS

-- 查詢擁有 'onepiece' 玩具的使用者姓名
select username from user where user_id in (select user_id from toy where toyname='onepiece');

連接查詢

交叉連接

select u.username, t.toyname from user as u cross join toy as t;
-- 笛卡兒積，第一表 5 筆 × 第二表 4 筆 = 20 筆結果

內連接

-- 相等連接
select u.username, t.toyname from user as u inner join toy as t on u.user_id = t.user_id;

-- 不等連接
select u.username, t.toyname from user as u inner join toy as t on u.user_id <> t.user_id order by u.username;

-- 自然連接（需同名欄位）
select u.username, t.toyname from user as u natural join toy as t;

外連接

-- 左外連接：以 user 為基準
select u.username, t.toyname from user u left outer join toy t on u.user_id = t.user_id;

-- 右外連接：以 toy 為基準
select u.username, t.toyname from user u right outer join toy t on u.user_id = t.user_id;

聯合查詢

-- UNION：合併並去重
select user_id from user union select user_id from toy order by user_id;

-- UNION ALL：合併保留重複
select user_id from user union all select user_id from toy order by user_id;

-- 建表：以聯合查詢結果建新表
create table m_union as select user_id from user union select user_id from toy;

集合運算

-- 交集：有玩具的小孩
select user_id from user intersect select user_id from toy;

-- 差集：沒有玩具的小孩
select user_id from user except select user_id from toy;