Menu
CoddyTech

Simplifique consultas com a palavra-chave WITH

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

As consultas podem ficar confusas demais quando adicionamos muitas consultas internas. Por exemplo, aqui está uma consulta que contém 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 (SELECT col5 FROM query1)
)
SELECT * FROM table1
WHERE col2 IN (SELECT col1 FROM query2) AND col4 IN (SELECT col5 FROM query1)

Aqui reutilizamos query1 em query2 e, novamente, na consulta principal para adicionar mais uma condição em col4, que a versão aninhada acima não tem. Uma consulta nomeada criada com WITH é chamada de CTE (Common Table Expression).

challenge icon

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:

  1. Primeiro identifique os "large devices" (dispositivos com width maior que 200)
  2. Em seguida, calcule a pontuação média desses dispositivos grandes. Chame essa coluna de average_score

Use a cláusula WITH para resolver este problema.

Experimente você mesmo

-- Etapa 1: dê um nome a um conjunto temporário de resultados que contenha apenas os dispositivos de seu interesse
WITH large_devices AS (
    SELECT ____
    FROM devices_specs
    WHERE ____
)
-- Etapa 2: reutilize esse conjunto temporário de resultados na consulta principal
SELECT ____ AS average_score
FROM devices_score
WHERE device_id IN (____);
quiz iconTeste seus conhecimentos

Esta lição inclui um quiz rápido. Comece a lição para respondê-lo e acompanhar seu progresso.

Todas as lições de Fundamentos

Pratique por conta própria: Playground de SQL