Voor het beantwoorden van sommige vragen zijn twee stappen nodig. “Welke bestellingen zijn bovengemiddeld?” vereist dat u eerst het gemiddelde vindt en vervolgens elke bestelling daarmee vergelijkt. Met SQL-subquery’s kunt u beide stappen als één instructie schrijven.

In deze zelfstudie wordt elk type behandeld met behulp van twee kleine tabellen. We hebben deze query’s uitgevoerd in SQLite 3.50 en de weergegeven resultaten zijn afkomstig van die run. De standaardsyntaxis van subquery’s wordt breed ondersteund en we wijzen op de punten waarop databases verschillen.

Wat is een subquery?

Een subquery, ook wel een innerlijke of geneste query genoemd, is een SELECT-instructie die tussen ronde haakjes in een andere instructie wordt geplaatst. Conceptueel gezien levert de binnenste query een resultaat op dat vervolgens door de buitenste query wordt gebruikt.

SELECT id, total
FROM orders
WHERE total > (SELECT AVG(total) FROM orders);

De voorbeeldtabellen

CREATE TABLE customers (id INT, name TEXT, city TEXT);
CREATE TABLE orders (id INT, customer_id INT, total INT);

INSERT INTO customers VALUES
 (1,'Asha','Pune'), (2,'Ben','Delhi'), (3,'Chitra','Pune'), (4,'Dev','Goa');

INSERT INTO orders VALUES
 (101,1,1200), (102,1,800), (103,2,300), (104,3,2500), (105,3,100);

Houd er rekening mee dat Dev geen orders heeft. Dat detail is van belang voor verschillende voorbeelden hieronder.

Soorten subquery’s in één oogopslag

Scalaire subquery’s

Een scalaire subquery retourneert precies één waarde, zodat u deze overal kunt gebruiken waar een enkele waarde zinvol is. Hier is de vraag van eerder, met het resultaat. Het gemiddelde van alle bestellingen is 980:

SELECT id, total
FROM orders
WHERE total > (SELECT AVG(total) FROM orders);

-- 101 | 1200
-- 104 | 2500

Je kon niet WHERE total > AVG(total) schrijven, omdat aggregaten niet rechtstreeks in WHERE zijn toegestaan. De subquery omzeilt dit door de waarde afzonderlijk te berekenen.

Wat als een ‘scalaire’ subquery meerdere rijen retourneert? De meeste databases, waaronder PostgreSQL, MySQL en SQL Server, geven een foutmelding. SQLite niet. In onze test gebruikte het stilletjes alleen de eerste rij, die een echte bug zou kunnen verbergen, dus vertrouw niet op dit gedrag.

IN en NIET IN

Wanneer de innerlijke query een lijst retourneert, gebruik dan IN. Dit vindt klanten met minstens één bestelling boven de 1000:

SELECT name FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE total > 1000);

-- Asha
-- Chitra

NOT IN doet het tegenovergestelde en vindt klanten die nog nooit hebben besteld:

SELECT name FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);

-- Dev

Dat werkt hier, maar NOT IN heeft een gevaarlijk randgeval, dat we hieronder bespreken.

Subquery’s in FROM

Een subquery in FROM fungeert als een tijdelijke tabel, vaak een afgeleide tabel genoemd. Dit is handig als u twee keer moet aggregeren, zoals in deze zoekopdracht voor de gemiddelde uitgaven per klant in elke stad:

SELECT city, AVG(spent) AS avg_spent
FROM (
  SELECT c.city, SUM(o.total) AS spent
  FROM customers c
  JOIN orders o ON o.customer_id = c.id
  GROUP BY c.id, c.city
) AS per_customer
GROUP BY city;

-- Delhi |  300.0
-- Pune  | 2300.0

De binnenste zoekopdracht telt het totaal van elke klant, en de buitenste telt het gemiddelde van deze totalen. Het is een goede gewoonte om de afgeleide tabel een alias te geven, aangezien sommige databases dit vereisen. Als GROUP BY nieuw voor je is, zie dan SQL GROUP BY en HAVING.

Subquery’s in SELECT

Een scalaire subquery kan ook een berekende kolom toevoegen. Deze telt bestellingen per klant:

SELECT name,
       (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) AS order_count
FROM customers c;

-- Asha   | 2
-- Ben    | 1
-- Chitra | 2
-- Dev    | 0

Dev verschijnt met nul. Een inner join zou Dev volledig hebben verwijderd, omdat Dev geen overeenkomende rijen heeft. Je zou hetzelfde resultaat kunnen krijgen met een LEFT JOIN en GROUP BY, wat we uitleggen in SQL-joins uitgelegd.

Gecorreleerde subquery’s

Het vorige voorbeeld is ook gecorreleerd, aangezien de binnenste query verwijst naar c.id uit de buitenste query. Een niet-gecorreleerde subquery kan alleen worden uitgevoerd. Een gecorreleerde kan dat niet, omdat deze afhankelijk is van een waarde uit de huidige buitenste rij.

Dit vindt bestellingen boven het eigen gemiddelde van diezelfde klant:

SELECT o.id, o.customer_id, o.total
FROM orders o
WHERE o.total > (
  SELECT AVG(i.total)
  FROM orders i
  WHERE i.customer_id = o.customer_id
);

-- 101 | 1 | 1200
-- 104 | 3 | 2500

Hier leest u hoe u het kunt lezen. Voor bestelling 101 berekent de interne zoekopdracht het gemiddelde van de bestellingen van klant 1 (1200 en 800), wat 1000 oplevert. Omdat 1200 groter is, wordt de rij behouden. Voor bestelling 102 geldt hetzelfde gemiddelde, maar 800 is niet groter en valt dus weg. De enkele bestelling van klant 2 is gelijk aan zijn eigen gemiddelde, dus deze wordt ook geschrapt.

“Eenmaal per rij” beschrijft de logica, en query-optimalisatieprogramma’s herschrijven deze vaak in iets veel efficiënter. Toch is een langzame gecorreleerde subquery het onderzoeken waard. Een index op de binnenste kolom, hier orders.customer_id, kan een groot verschil maken. Zie SQL-indexen uitgelegd.

BESTAAT en BESTAAT NIET

EXISTS stelt een ja of nee-vraag: retourneert de innerlijke query minstens één rij? Het kan stoppen bij de eerste match, en wat je daarin selecteert, doet er niet toe. Daarom schrijven mensen vaak SELECT 1.

SELECT c.name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

-- Asha, Ben, Chitra

SELECT c.name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

-- Dev

De NOT IN NULL-val

Dit is een van de meest voorkomende subquery-bugs. We hebben een tabel gemaakt met één echte ID en één NULL, en vervolgens beide formulieren erop uitgevoerd:

CREATE TABLE r (customer_id INT);
INSERT INTO r VALUES (1), (NULL);

SELECT name FROM customers
WHERE id NOT IN (SELECT customer_id FROM r);
-- (no rows at all)

SELECT c.name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM r WHERE r.customer_id = c.id);
-- Ben, Chitra, Dev

Waarom levert de eerste niets op? id NOT IN (1, NULL) betekent id <> 1 AND id <> NULL. Elke vergelijking met NULL is eerder onbekend dan waar, dus de hele voorwaarde kan nooit waar zijn. Er is geen foutmelding en geen waarschuwing, alleen een leeg resultaat dat er plausibel uitziet.

Subquery versus JOIN versus CTE

Veel query’s kunnen op meer dan één manier worden geschreven. Eerder vonden we klanten met een grote bestelling via IN. Hier is hetzelfde resultaat met een join:

SELECT DISTINCT c.name FROM customers c
JOIN orders o ON o.customer_id = c.id AND o.total > 1000;

-- Asha
-- Chitra

Let op de DISTINCT. Een join kan duplicaten opleveren als een klant meerdere matches heeft, terwijl IN en EXISTS dat niet kunnen.

De prestaties variëren per database en data, en moderne optimizers produceren vaak vergelijkbare plannen. Kies dus eerst de duidelijkste vorm en vink vervolgens EXPLAIN aan als deze langzaam blijkt te zijn.

Veel voorkomende fouten

• Gebruik = met een resultaat met meerdere rijen. Gebruik IN of zorg ervoor dat de binnenste query één rij retourneert.
• Gebruik NOT IN voor een null-kolom. Je kunt elke rij stilletjes kwijtraken.
• Gebruik onduidelijke aliassen. Bij gecorreleerde zoekopdrachten moet u elke kolom kwalificeren, anders kan deze aan de verkeerde tabel worden gekoppeld.
• Diepe nesting. Drie niveaus zijn moeilijk te lezen, dus converteer ze naar CTE’s.
• Het toevoegen van ORDER BY aan de binnenkant. Dit heeft meestal geen effect op het uiterlijke resultaat, en sommige databases wijzen dit af.

Tot slot

Subquery’s zijn een natuurlijke manier om vragen in twee stappen uit te drukken. Begin met scalaire subquery’s en IN, en oefen vervolgens met de gecorreleerde subquery’s door één buitenste rij tegelijk te doorlopen. En onthoud NOT EXISTS wanneer NULL’s mogelijk zijn. Om te oefenen kunt u een vraag op drie manieren schrijven, met een subquery, een join en een CTE, en zowel de resultaten als de leesbaarheid vergelijken.

Veelgestelde vragen

Blijf leren