Lär dig SQL

Back to All Courses

Lesson 8

Subqueries och CTE

by Ted Klein Bergman

Subqueries

En subquery är egentligen bara en helt vanlig query, fast den används i en annan query. Detta visas enklast med ett exempel. Om ni kommer ihåg från förra kapitlet så skrev vi en query som gav oss användarnamnet på den användare som hade postat tweeten med ID-numret 15.

SELECT username
FROM   users CROSS JOIN tweets
WHERE  tweet_id = 15 AND user_id = poster_id;

Ett annat sätt att tänka på detta problem är att dela upp det i två olika queries. Först vill vi hitta vem som postade vår tweet, och sen vill vi få fram användarens namn. Detta är två queries varav vardera agerar på endast en tabell.

-- Ger oss ID-numret på den som postade tweeten med ID:et 15.
SELECT poster_id
FROM   tweets
WHERE  tweet_id = 15;

-- Ger oss användarnamnet på användaren med ID:et x. Notera att x i detta 
-- fall ska bytas ut mot resultatet vi får från första queryn.
SELECT username
FROM   users
WHERE  user_id = x;

Vad vi gör i detta fall är helt enkelt att byta ut x mot vår första query. Dock är det viktigt att komma ihåg att subqueries alltid måste omges av parenteser.

SELECT username
FROM   users
WHERE  user_id = (SELECT poster_id FROM tweets WHERE tweet_id = 15);

I vanliga fall brukar vi bryta upp koden så varje nyckelord hamnar på ny rad, men med subqueries ser det lite konstigt ut så vi skrev den på en rad då den var så kort. Använd vanligt vett i denna situation och skriv på det sätt som är enklast att läsa.

En sak som är viktig och tänka på är vad en subquery returnerar. Den kan antingen returnera en skalär (ett enskilt värde), en rad/kolumn eller en tabell. I exemplet ovan visste vi att en skalär skulle returneras (eftersom tweet_id är unikt då det är en PRIMARY KEY, så det kan endast finnas en tweet med ID:et 15) så vi kunde jämföra resultatet med vårat user_id. Om subqueryn skulle returnera något annat än en skalär hade vi fått ett felmeddelande!

Om en subquery returnerar en skalär kan vi jämföra den precis som vi lärde oss i kapitlet Datatyper och villkor. Om den istället returnerar en rad/kolumn så finns ett par funktioner vi kan använda oss av.

  1. EXISTS (<subquery>) tittar ifall subqueryn returnerar någonting eller inte.

  2. <expression> IN (<subquery>) tittar ifall ett uttryck existerar i en subquery. Ett uttryck är något som resulterar i ett tal.

Ett konkret exempel kan vara att lista alla användare som har postat något tweet.

SELECT username
FROM   users
WHERE  user_id IN (SELECT poster_id FROM tweets);

Eller hitta namn på alla användare som postat tweets som innehåller ett e.

SELECT username
FROM   users
WHERE  EXISTS(SELECT * FROM tweets WHERE user_id = poster_id AND content LIKE '%e%');

Den senaste subqueryn är en så kallad correlated subquery. Mer om det kommer nedan.

Subqueries i FROM satsen

Om en subquery returnerar en tabell, eller en kolumn, kan den användas i FROM satsen. Den kan då antingen användas för sig självt eller sammanfogas med andra tabeller/subqueries. Ett problem är dock att subqueries alltid måste ges ett namn. Detta för att man ska kunna hänvisa till subqueryn ifall dess attribut skulle ha samma namn som ett attribut från en annan tabell. Man namnger en subquery genom att helt enkelt skriva ett namn efter den.

Kommande exempel hittar alla användare med minst en följare.

SELECT username
FROM   users 
JOIN   (SELECT DISTINCT user_id FROM followers) users_with_followers
ON     users.user_id = users_with_followers.user_id;

I exemplet ovan joinar vi vår tabell Users med en subquery vi kallar users_with_followers. Vi skriver alltså ut användarnamnet på alla användare som har följare. Vi vet också att user_id förekommer en gång för varje följare de har, så därför använder vi DISTINCT för att ta bort dubbletter.

Correlated subquery

Det går att använda attribut från den yttre queryn i subqueryn, vilket gör dem korrelerade (d.v.s. att subqueryn inte är självständig, utan beroende av den yttre queryn). Ett exempel kan vara att hitta vilka användare som har postat mer tweets än deras ålder.

SELECT username
FROM   users 
WHERE  age > (SELECT COUNT(*) FROM tweets WHERE poster_id = user_id);

Man kan föreställa sig att det kommer agera som nested for-loops. Varje user_id i users kommer att jämföras emot varje poster_id i tweets. I Python skulle det se ut likt följande:

for user in users:
    count = 0
    for tweet in tweets:
        if tweet.poster_id == user.user_id:
              count += 1
    if user.age > count:
        print(user.username)

CTE

Problemet med subqueries är att det kan vara svårt att läsa om man har för många subqueries djupt i varandra. En lösning kan vara att skapa CTE (Common Table Expression) istället. En CTE är en temporär tabell vi skapar för våran query.

Syntaxen för en CTE är:

WITH <name> AS (
    <query>
)

  1. <name>  är namnet på våran tillfälliga tabell.

  2. <query> är en query.

Om vi tar vårt exempel där vi ville hitta alla användare med följare så skulle vi kunna använda oss av en CTE på följande vis:

WITH users_with_followers As (
  SELECT DISTINCT user_id 
  FROM followers
)                    -- NOTERA: Inget semikolon, då CTE är en del av kommandot!

SELECT username
FROM   users JOIN users_with_followers
ON     users.user_id = users_with_followers.user_id;

Alla attribut som queryn i vår CTE returnerar kommer gå att hämta som om vår CTE var en tabell med just de attributen. I många fall blir detta mycket lättare att läsa och lösa problem med då man kan fokusera på en del i taget.