SQL Subqueries: volledige uitleg, voorbeelden & tutorial

Introductie in SQL subqueries

Een SQL subquery is een SQL query die in een andere query wordt uitgevoerd binnen een relationele database. Subqueries kunnen de boel onoverzichtelijk maken, waardoor het handig is om parrallel kennis op te doen van common table expressions (CTEs).

Waarom subqueries handig zijn, bekijken we vanuit een uitgebreide uitleg met voorbeelden.

Wil je op je eigen computer meedoen met deze tutorial? Volg dan deze stappen om SQL en PgAdmin te installeren en de voorbeeld IMDb-database in te laden.

In deze blog komen onderstaande onderwerpen aan de orde. Klik op een onderwerp om er direct naartoe te navigeren.

• SQL Subquery in een WHERE statement
• SQL Subquery in een FROM statement
• SQL Subquery met een EXISTS statement
• SQL Subquery met een IN statement
• SQL Subquery met een ANY statement
• SQL Subquery met een ALL statement

SQL Subquery in een WHERE statement

We gaan een voorbeeld bekijken vanuit tabellen movies en roles. We beginnen met een subquery in een WHERE statement.

Op deze pagina vind je een uitgebreide omschrijving van deze dataset

In dit voorbeeld willen we het volgende:

• Een selectie van films uit tabel movies.
• Daar waar de lengte van de naam van een film, groter is dan de gemiddelde lengte van de namen van filmrollen.

Om tot een antwoord te komen dienen we eerst uit te zoeken hoe we aan de gemiddelde lengte van de namen van filmrollen komen.

Tabel roles bevat details van filmrollen.

• We gebruiken functies ROUND() (afronden), AVG() (gemiddelde) en LENGHT().
• Om de gemiddelde lengte van namen van filmrollen te berekenen.

Lees hier meer over SQL aggregate functions

SELECT
  ROUND(AVG(LENGTH(role)))
FROM
  roles;

De gemiddelde lengte van de naam van een filmrol bedraagt 14 tekens.

Nu we weten hoe lang de gemiddelde naam van een filmrol is kunnen we dit gaan gebruiken om te kijken welke films een langere naam hebben dan dit gemiddelde.

Tabel movies bevat details van films.

• We gebruiken een WHERE statement.
• Om een selectie te maken op de lengte van filmnamen. Daar waar de naam groter is dan 14 tekens.

SELECT
  id,
  name,
  LENGTH(name) AS name_length
FROM
  movies
WHERE
  LENGTH(name) > 14
ORDER BY
  name_length;

Columns: 3
Rows: 10

We zien dat alleen films met een naam met meer dan 14 tekens zijn geslecteerd.

Wat is hier het probleem?

In plaats van getal 14 hardcoded te benoemen, zou het mooi zijn om dit met een query te verkrijgen.

Dit kan echter niet zomaar.

Hier hebben we een SQL subquery voor nodig.

subquery in WHERE statement

We gebruikten de volgende query:

SELECT
  id,
  name,
  LENGTH(name) AS name_length
FROM
  movies
WHERE
  LENGTH(name) > 14
ORDER BY
  name_length;

Met hierin getal 14 hardcoded.

In onderstaande query vervangen we getal 14 door een andere query.

We gebruiken hiervoor de query die weeerder gebruikten om het getal 14 te berekenen.

SELECT
  id,
  name,
  LENGTH(name) AS name_length
FROM
  movies
WHERE
  LENGTH(name) > (
    SELECT
      ROUND(AVG(LENGTH(role)))
    FROM
      roles
  )
ORDER BY
  name_length;

Columns: 3
Rows: 10

We zien hetzelfde resultaat als eerder. Je ziet hiermee dat we een query binnen een query uit kunnen voeren. Dit heet een subquery.

Subquery in een FROM statement

Zojuist hebben we met SQL een subquery vanuit het WHERE statement bekeken. Hiermee konden we de uitkomst van een subquery gebruiken in een filtering.

We hebben tot nu toe de lengte van een filmnaam vergeleken met de gemiddele lengte van filmrolnamen.

Stel dat we nu die gemiddelde lengte willen toevoegen als kolom. Dit kan niet zomaar, omdat het resultaat alleen beschikbaar is in het WHERE statement.

We kunnen dit oplossen met een subquery in het FROM statement.

We gebruikten de volgende query:

SELECT
  m.id,
  m.name,
  LENGTH(m.name) AS name_length,
  r.*
FROM
  movies AS m,
  (
    SELECT
      ROUND(AVG(LENGTH(role))) AS role_avg_length
    FROM
      roles
  ) AS r
WHERE
  LENGTH(m.name) > r.role_avg_length;

Columns: 3
Rows: 10

We zien dat de gemiddelde lengte nu als kolom is toegevoegd. Dit door een subquery vanuit het FROM statement.

Wat weten we tot nu toe over subqueries?

Je kunt een query binnen een andere query uitvoeren. Dit heet een subquery. Dit kan op allerlei plaatsen, bijvoorbeeld in het WHERE, FROM, of SELECT statement.

SQL Subquery met een EXISTS statement

Om aan te tonen wanneer een subquery met een EXISTS statement handig is, zullen we werken met een nieuw voorbeeld.

Stel dat we het volgende willen bereiken:

• Een selectie van films uit tabel movies.
• Alleen de films met genre Horror.

Dit zouden we als volgt op kunnen lossen:

• Tabellen movies en movies_genres samenvoegen met een LEFT JOIN.
• Filteren op genre Horror.
• Alleen kolommen uit tabel movies selecteren.

SELECT
  movies.*
FROM
  movies
  LEFT JOIN movies_genres ON movies_genres.movie_id = movies.id
WHERE
  genre = 'Horror';

Columns: 4
Rows: 3

De output toont hier echter geen data uit tabel movies_genres. Hierdoor is het minder netjes om een JOIN te gebruiken. Je hebt de gekoppelde data immers niet nodig. Het is daarom netter om een subquery te gebruiken in combinatie met het EXISTS statement.

In het voorbeeld wilden we alleen films met genre Horror.

Met een subquery en het EXISTS statement kunnen we dit vraagstuk oplossen. Dit kan zonder dat een JOIN nodig is.

Onderstaande query doet dit:

SELECT
  *
FROM
  movies
WHERE
  EXISTS (
    SELECT
      *
    FROM
      movies_genres
    WHERE
      movies.id = movies_genres.movie_id
      AND genre = 'Horror'
  );

Columns: 4
Rows: 3

We zien hetzelfde resultaat als toen we de LEFT JOIN en filtering gebruikten.

Een subquery met EXISTS werkt als volgt:

SELECT
  <column_name(s)>
FROM
  <table_name_1>
WHERE
  EXISTS (
    SELECT
      <column_name(s)>
    FROM
      <table_name_2>
    WHERE
      <condition(s)>
  );

• Je haalt informatie op uit een tabel. In ons geval tabel movies.
• Alleen daar waar (WHERE) een match is, met EXISTS en een subquery.
• In ons geval:
  • Tabel movies_genres.
  • Bij genre = 'Horror'.
  • En de match movies.id = movies_genres.movie_id.

Hierdoor pas je filtering toe op tabel movies. Dit baseer je op een verband met de tabel movies_genres.

De performance is vergelijkbaar. Het maakt hier qua rekenkracht/tijd nauwelijks uit of je de query met LEFT JOIN of EXISTS gebruikt.

Samenvatting subquery met EXISTS

• Een subquery is een query die in een andere query wordt uitgevoerd.
• Dit kan bijvoorbeeld met EXISTS. Daarmee filter je op de voorkomendheid van een bepaalde match.
• Voorbeeld:

SELECT
  *
FROM
  movies
WHERE
  EXISTS (
    SELECT
      *
    FROM
      movies_genres
    WHERE
      movies.id = movies_genres.movie_id
      AND genre = 'Horror'
  );

Subqueries met IN, ANY en ALL

Met een subquery met EXISTS filter je op de voorkomendheid van een bepaalde match.

Hiernaast zijn er ook andere mogelijkheden:

• IN: filtering op enige voorkomendheid (in een lijst).
• ANY: filtering op enige voorkomendheid.
• ALL: filtering op elke voorkomendheid.

Deze opties zullen we nu om de beurt behandelen.

SQL Subquery met een IN statement

Met het IN statement en een subquery doen we het volgende:

• Filtering op enige voorkomendheid (in een lijst).

We gebruiken dit om hetzelfde resultaat als zojuist te verkrijgen:

• Alleen films met genre Horror.

SELECT
  *
FROM
  movies
WHERE
  movies.id IN (
    SELECT
      movie_id
    FROM
      movies_genres
    WHERE
      genre = 'Horror'
  );

Columns: 4
Rows: 3

We zien hetzelfde resultaat als eerder met:

• Subquery met EXISTS.

We doen daarbij nu het volgende:

• De subquery haalt een lijst op van movie_id's die genre 'Horror' hebben.
• Met IN filteren we alleen de ids uit deze lijst.

SQL Subquery met een ANY statement

Met het ANY statement en een subquery doen we het volgende:

• Filtering op enige voorkomendheid.

We gebruiken dit om hetzelfde resultaat als zojuist te verkrijgen:

• Alleen films met genre Horror.

SELECT
  *
FROM
  movies
WHERE
  movies.id = ANY (
    SELECT
      movie_id
    FROM
      movies_genres
    WHERE
      genre = 'Horror'
  );

Columns: 4
Rows: 3

We zien hetzelfde resultaat als eerder met:

• Subquery met EXISTS.
• Subquery met IN.

We doen daarbij nu het volgende:

• De subquery haalt een lijst op van movie_id's die genre 'Horror' hebben.
• Met ANY filteren we alleen de id's uit deze lijst.

SQL Subquery met een ALL statement

Met het ALL statement en een subquery doen we het volgende:

• Filtering op elke voorkomendheid.

We gebruiken dit voor een nieuwe voorbeeld:

• Alleen de film die als enige genre War heeft.

SELECT
  *
FROM
  movies
WHERE
  movies.id = ALL (
    SELECT
      movie_id
    FROM
      movies_genres
    WHERE
      genre = 'War'
  );

Columns: 4
Rows: 1

De film Braveheart is dus de enige film met genre 'War'.

We doen daarbij nu het volgende:

• De subquery haalt een lijst op van movie_id's die genre 'War' hebben. Dit is maar één id.
• Met ALL filteren we de id's uit deze lijst. Op elke/volledige overeenkomst met de lijst.

Het gebruik van ALL komt mogelijk wat abstract/onhandig op je over. Dit zul je in de praktijk niet vaak gebruiken.

Wil je nog veel meer leren over SQL? Bekijk dan onze SQL cursus voor data analyse of download onderstaande opleidingsbrochure. In onze SQL cursus wisselen theorie en praktijkoefening elkaar steeds af. Nadat je deze training hebt gevolgd kun je zelfstandig met SQL werken. Je kunt ook altijd even contact opnemen via info@datasciencepartners.nl of 020 - 24 43 146 als je een vraag hebt.