Simplify queries, WITH keyword

Parte da seção Fundamentos do Journey de SQL da Coddy — lição 49 de 72.

As consultas podem ficar muito bagunçadas ao adicionar muitas consultas internas. Por exemplo, aqui está uma consulta que possui muitas subconsultas:

SELECT * FROM table1
WHERE col2 IN (
	SELECT col1 FROM table2
	WHERE col3 + col2 > 3 AND col5 LIKE '%test%' AND col6 IN (
		SELECT col5 FROM table3
		WHERE col1 AND col3 OR col2
	)
)

Para facilitar, podemos usar a palavra-chave WITH query_name AS (...). Ela nos permite salvar uma consulta com um nome e usá-la onde quisermos:

WITH query1 AS (
	SELECT col5 FROM table3
	WHERE col1 AND col3 OR col2
), query2 AS (
	SELECT col1 FROM table2
	WHERE col3 + col2 > 3 AND col5 LIKE '%test%' AND col6 IN query1
)
SELECT * FROM table1
WHERE col2 IN (SELECT col1 FROM query2) AND col4 IN (SELECT col5 FROM query1)

Aqui reutilizamos query1 em query2 e na consulta principal.

Desafio

Fácil

Tabelas e colunas disponíveis:
devices_specs: device_id, width, height, num_features, opinion
devices_score: device_id, score

escreva uma consulta que:

Primeiro identifique "large devices" (dispositivos com width maior que 200)
Em seguida, calcule a pontuação média para esses dispositivos grandes. Nomeie esta coluna como average_score

Use a cláusula WITH para resolver este problema.

Folha de consulta

Use WITH query_name AS (...) para criar subconsultas nomeadas que podem ser reutilizadas:

WITH query1 AS (
    SELECT col5 FROM table3
    WHERE col1 AND col3 OR col2
), query2 AS (
    SELECT col1 FROM table2
    WHERE col3 + col2 > 3 AND col5 LIKE '%test%' AND col6 IN query1
)
SELECT * FROM table1
WHERE col2 IN query2 AND col4 IN query1

Esta abordagem torna consultas complexas com múltiplas subconsultas mais legíveis e permite reutilizar a mesma subconsulta várias vezes.

Experimente você mesmo