Sottoquery e common table expressions (CTE)

Migliorare le prestazioni delle query in PostgreSQL

Amy McCarty

Instructor

Sulle sottoquery

Cosa?

Alternativa alle join
Query semplice

Perché?

Può restituire un solo risultato
Leggibile
Istruzioni SQL simili alle join

Come?

Nelle clausole SELECT, FROM o WHERE

Sottoquery in 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	...	...

Sottoquery in 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

Sottoquery in 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	...	...

Sottoquery in WHERE

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

avg_movie
3

Sottoquery in FROM

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

Riduce la leggibilità
Limita la flessibilità del piano di query

Le common table expressions (CTE)

Cosa?

Alternativa alle join
Query autonoma con risultati temporanei

Perché?

Può restituire un solo risultato
Leggibile
Crea una tabella temporanea

Come?

Istruzioni WITH

Struttura di una 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

Let's practice!

Migliorare le prestazioni delle query in PostgreSQL