子查詢與共用資料表運算式（CTE）

改進 PostgreSQL 的查詢效能

Amy McCarty

Instructor

關於子查詢

是什麼？

join 的替代方案
簡單查詢

為什麼？

可只回傳一個結果
可讀性佳
SQL 指令與 join 類似

怎麼做？

在 SELECT、FROM 或 WHERE 子句中

SELECT 子查詢

row	script_word	word_length
1	goat	4
2	goat	4
3	dog	3
15,782	...	...

row	english_word	word_length
1	goat	4
2	turkey	6
3	ant	3
171,476	...	...

SELECT 子查詢

SELECT AVG(word_length) AS avg_movie
  , (SELECT AVG(word_length) 
      FROM english_language) 
      AS avg_english
FROM MOVIE

avg_movie	avg_english
3	4.5

WHERE 子查詢

row	script_word	word_length
1	goat	4
2	goat	4
3	dog	3
15,782	...	...

row	english_word	word_length
1	goat	4
2	turkey	6
3	ant	3
171,476	...	...

WHERE 子查詢

SELECT AVG(word_length) AS avg_movie
FROM english_language
WHERE word IN 
  (SELECT DISTINCT word FROM movie)

avg_movie
3

FROM 子查詢

SELECT AVG(word_length) AS avg_movie 
FROM (SELECT * FROM movie)

降低可讀性
侷限查詢規劃彈性

關於共用資料表運算式（CTE）

是什麼？

join 的替代方案
獨立查詢，產生暫存結果集

為什麼？

可只回傳一個結果
可讀性佳
建立暫存資料表

怎麼做？

使用 WITH 陳述式

CTE 結構

WITH english_cte AS
(    
  SELECT word_length
      , COUNT(word) AS english_word_count
    FROM english_language 
    GROUP BY word_length
)

SELECT movie.word_length
  , COUNT(movie.word) AS movie_word_count
  , cte.english_word_count
FROM movie
INNER JOIN english_cte cte 
ON movie.word_length = cte.word_length
GROUP BY movie.word_length, cte.english_word_count

一起來練習吧！

改進 PostgreSQL 的查詢效能