子查詢用好用滿！最佳實務！

SQL 資料操作

Mona Khalil

Data Scientist, Greenhouse Software

想放幾個子查詢都行…

可在 SELECT、FROM、WHERE 放多個子查詢

SELECT 
    s.stage,
    ROUND(s.avg_goals,2) AS avg_goal,
    (SELECT AVG(home_goal + away_goal)
     FROM match WHERE season = '2013/2014') AS overall_avg
FROM 
    (SELECT
         stage,
         AVG(home_goal + away_goal) AS avg_goals
     FROM match
     WHERE season = '2013/2014'
     GROUP BY stage) AS s
WHERE 
    s.avg_goals > (SELECT AVG(home_goal + away_goal) 
                   FROM match WHERE season = '2013/2014');

格式化你的查詢

對齊 SELECT、FROM、WHERE、GROUP BY

SELECT
    col1,
    col2,
    col3
FROM table1
WHERE col1 = 2;

為查詢加註解

/* 這個查詢篩選 col1 = 2，
且只選取 table1 的資料 */
SELECT
    col1,
    col2,
    col3
FROM table1
WHERE col1 = 2;

為查詢加註解

SELECT
    col1,
    col2,
    col3
FROM table1 -- 這個資料表有 10,000 列
WHERE col1 = 2; -- 在 WHERE 篩選值 2

為查詢做縮排

子查詢要縮排！

SELECT
    col1,
    col2,
    col3
FROM table1
WHERE col1 IN
        (SELECT id
         FROM table2
         WHERE year = 1991);

為查詢做縮排

SELECT 
  date, 
  hometeam_id, 
  awayteam_id,
  CASE WHEN hometeam_id = 8455 AND home_goal > away_goal 
            THEN 'Chelsea home win'
       WHEN awayteam_id = 8455 AND home_goal < away_goal
            THEN 'Chelsea away win'
       WHEN hometeam_id = 8455 AND home_goal < away_goal
            THEN 'Chelsea home loss'
       WHEN awayteam_id = 8455 AND home_goal > away_goal
            THEN 'Chelsea away loss'
       WHEN (hometeam_id = 8455 OR awayteam_id = 8455) 
            AND home_goal = away_goal THEN 'Chelsea Tie'
       END AS outcome
FROM match
WHERE hometeam_id = 8455 OR awayteam_id = 8455;

Holywell 的 SQL 風格指南

子查詢有必要嗎？

子查詢需要運算資源
- 你的資料庫多大？
- 你要查的資料表多大？
這個子查詢真的必要嗎？

正確地為每個子查詢加上篩選！

留意你的篩選條件！

SELECT 
    s.stage,
    ROUND(s.avg_goals,2) AS avg_goal,
    (SELECT AVG(home_goal + away_goal)
     FROM match WHERE season = '2013/2014') AS overall_avg
FROM 
    (SELECT
         stage,
         AVG(home_goal + away_goal) AS avg_goals
     FROM match
     WHERE season = '2013/2014'
     GROUP BY stage) AS s
WHERE 
    s.avg_goals > (SELECT AVG(home_goal + away_goal) 
                   FROM match WHERE season = '2013/2014');

一起來練習吧！

SQL 資料操作