Poddotazy a společné tabulkové výrazy

Introduction to Snowflake SQL

George Boorman

Senior Curriculum Manager, DataCamp

Poddotazy

Vnořené dotazy
Použití v klauzulích FROM, WHERE, HAVING nebo SELECT

Příklad:

SELECT column1 
FROM table1 
WHERE column1 = (SELECT column2 FROM table2 WHERE condition)

Typy: korelované a nekorelované poddotazy

Nekorelovaný poddotaz

-- Main query returns pizzas priced at the maximum value found in the subquery
SELECT pizza_id
FROM pizzas
-- Uncorrelated subquery that identifies the highest pizza price
WHERE price = (
    SELECT MAX(price)
    FROM pizzas
)

Poddotaz nepracuje s hlavním dotazem

Korelovaný poddotaz

Poddotaz odkazuje na sloupce hlavního dotazu

SELECT pt.name, 
       pz.price, 
       pt.category
FROM pizzas AS pz
JOIN pizza_type AS pt 
    ON pz.pizza_type_id = pt.pizza_type_id
WHERE pz.price = (
  -- Identifies max price for each pizza category
    SELECT MAX(p2.price) -- Max price
    FROM pizzas AS p2
    WHERE -- Correlated: uses outer query column 
      p2.pizza_type_id = pz.pizza_type_id
)

Společné tabulkové výrazy

Obecná syntaxe:

-- WITH keyword
WITH cte1 AS ( -- CTE name
        SELECT col_1, col_2
            FROM table1
    )
    ...
SELECT ... 
FROM cte1 -- Query CTE
;

Společné tabulkové výrazy

WITH max_price AS ( -- CTE called max_price
    SELECT pizza_type_id, 
           MAX(price) AS max_price
    FROM pizzas
    GROUP BY pizza_type_id
)

-- Main query 
SELECT pt.name, 
       pz.price, 
       pt.category
FROM pizzas AS pz
JOIN pizza_type AS pt ON pz.pizza_type_id = pt.pizza_type_id
JOIN max_price AS mp  -- Joining with CTE max_price
     ON pt.pizza_type_id = mp.pizza_type_id 
WHERE pz.price < mp.max_price -- Compare the price with max_price CTE column

Více CTE

-- Define multiple CTEs separated by commas
WITH cte1 AS (
    SELECT ...
    FROM ...
),

cte2 AS (
    SELECT ...
    FROM ...
)

-- Main query combining both CTEs
SELECT ...
FROM cte1
JOIN cte2 ON ...
WHERE ...

Proč používat CTE?

Správa složitých operací
Modulárnost
Čitelnost
Znovupoužitelnost

Pojďme si procvičit!

Introduction to Snowflake SQL