Subkueri dan common table expressions (CTE)

Meningkatkan Performa Kueri di PostgreSQL

Amy McCarty

Instructor

Tentang subkueri

Apa?

Alternatif join
Kueri sederhana

Mengapa?

Dapat mengembalikan satu hasil
Mudah dibaca
Instruksi SQL mirip join

Bagaimana?

Di klausa SELECT, FROM, atau WHERE

Subkueri di SELECT

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

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

Subkueri di 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

Subkueri di WHERE

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

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

Subkueri di WHERE

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

avg_movie
3

Subkueri di FROM

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

Menurunkan keterbacaan
Membatasi fleksibilitas rencana kueri

Tentang common table expressions (CTE)

Apa?

Alternatif join
Kueri mandiri dengan set hasil sementara

Mengapa?

Dapat mengembalikan satu hasil
Mudah dibaca
Membuat tabel sementara

Bagaimana?

Pernyataan WITH

Struktur 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

Ayo berlatih!

Meningkatkan Performa Kueri di PostgreSQL