SQL-subquery: een complete gids

SQL-subqueries zijn een krachtig hulpmiddel in databasebeheer en maken complexere en efficiëntere gegevensopvraging mogelijk. Deze gids neemt je mee door de basisprincipes van SQL-subqueries en biedt inzicht in hun praktische toepassingen en geavanceerde technieken. Of je nu een beginner bent of een ervaren professional, het onder de knie krijgen van subqueries kan je SQL-vaardigheden flink versterken.

Ben je nieuw in SQL, begin dan met onze cursus Intermediate SQL om een sterke basis op te bouwen. Ook vind ik de SQL Basics Cheat Sheet, die je kunt downloaden, een handig naslagwerk omdat alle meest voorkomende SQL-functies erin staan. Tot slot: subqueries zijn een veelvoorkomende SQL-sollicitatievraag, dus als je je voorbereidt op een interview, ben je hier op de juiste plek voor een opfrisser.

Wat is een SQL-subquery?

Een subquery maakt SQL-queries modulairder door taken af te handelen die anders meerdere losse queries zouden vereisen.

Definitie en doel

Een SQL-subquery is een query die is genest binnen een andere SQL-query en wordt gebruikt voor bewerkingen die meerdere stappen of complexe logica vereisen. De rol van subqueries in SQL omvat het volgende:

• Records filteren op basis van gegevens uit gerelateerde tabellen.
• Gegevens aggregeren en dynamisch berekeningen uitvoeren.
• Gegevens kruisverwijzen tussen tabellen om specifieke inzichten op te halen.
• Voorwaardelijk rijen selecteren zonder expliciete joins of externe codelogica.

Het klinkt als veel, maar het wordt duidelijker terwijl we dit in de tutorial verkennen.

Typen subqueries

Misschien verrast het je dat er verschillende typen subqueries zijn. De verschillende typen zijn gegroepeerd naar en geschikt voor verschillende soorten data-opvragingen. Afhankelijk van de bewerking die je wilt uitvoeren, kun je kiezen uit de volgende subqueries:

Scalare subqueries

Scalare subqueries geven één waarde terug, zoals één rij en één kolom. Ze worden vaak gebruikt waar een enkele waarde wordt verwacht, bijvoorbeeld in berekeningen, vergelijkingen of toewijzingen in SELECT- of WHERE-clausules.

In het onderstaande voorbeeld retourneert de scalare subquery (SELECT AVG(salary) FROM employees) één waarde, het gemiddelde salaris, en vergelijkt die met het salaris van elke werknemer.

-- Example of Scalar Subquery 
-- Compares each salary to the average salary
SELECT employee_name, 
       salary,
       (SELECT AVG(salary) FROM employees) AS average_salary 
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Kolomsubqueries

Kolomsubqueries retourneren één kolom maar meerdere rijen. Deze subqueries worden vaak gebruikt met operatoren als IN of ANY, waarbij de buitenste query waarden uit meerdere rijen vergelijkt.

Zo retourneert de subquery hieronder een lijst met afdelings-ID’s voor afdelingen in New York, die de hoofdquery vervolgens gebruikt om werknemers in die afdelingen te filteren.

-- Example of Column Subquery 
-- Filters based on departments in New York
SELECT employee_name
FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE location = 'New York');

Rijsubqueries

Rijsubqueries retourneren één rij met meerdere kolommen. Deze subqueries worden doorgaans gebruikt met vergelijkingsoperatoren die een rij gegevens kunnen vergelijken, zoals de operatoren = of IN, wanneer meerdere waarden worden verwacht.

De volgende subquery haalt de afdeling en functietitel van een manager op, en de buitenste query zoekt werknemers met overeenkomende waarden.

-- Example of Row Subquery 
-- Matches department and job title with a specific manager
SELECT employee_name
FROM employees
WHERE (department_id, job_title) = (SELECT department_id, job_title FROM managers WHERE manager_id = 1);

Tabelsubqueries (afgeleide tabellen)

Tabelsubqueries, of afgeleide tabellen, geven een complete tabel met meerdere rijen en kolommen terug. Deze worden vaak gebruikt in de FROM-clausule als een tijdelijke tabel binnen een query.

In het onderstaande voorbeeld maakt de subquery een afgeleide tabel met gemiddelde salarissen per afdeling, die vervolgens in de buitenste query wordt gebruikt om afdelingen te vinden met een gemiddeld salaris boven een bepaalde drempel.

-- Example of Table Subquery 
-- Uses derived table for average department salary comparison
SELECT dept_avg.department_id, dept_avg.avg_salary
FROM 
    (SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id) AS dept_avg
WHERE dept_avg.avg_salary > 50000;

SQL-subqueries begrijpen

In de bovenstaande voorbeelden zagen we dat een subquery tussen haakjes staat. Laten we nu de werking van een subquery bekijken, inclusief de syntaxis en de uitvoervolgorde.

Syntaxis en structuur

De syntaxis van een subquery varieert afhankelijk van waar deze wordt gebruikt in de hoofd-SQL-instructie, zoals binnen SELECT-, FROM- of WHERE-clausules. Subqueries staan doorgaans tussen haakjes ( ), waarmee een aparte query wordt aangegeven. 

Het volgende voorbeeld laat een subquery binnen een WHERE-clausule zien, waarmee we gegevens in de hoofdquery kunnen filteren op basis van de resultaten van een geneste query. 

-- Selects the main column to retrieve from the main table to query
SELECT column_name
FROM table_name
-- Applies a condition to filter rows based on the subquery result
WHERE column_name operator 
      -- Subquery retrieves data for comparison in the WHERE clause
      (SELECT column_name FROM table_name WHERE condition);  

Uitvoervolgorde

De uitvoervolgorde voor subqueries hangt af van of ze gecorreleerd of niet-gecorreleerd zijn.

Niet-gecorreleerde subqueries

Niet-gecorreleerde subqueries zijn onafhankelijk van de buitenste query en worden eerst uitgevoerd. Het resultaat van de subquery wordt vervolgens doorgegeven aan de buitenste query. Niet-gecorreleerde subqueries worden vaak gebruikt voor scalaire of kolomniveau-berekeningen en filters.

De query hieronder volgt de uitvoervolgorde:

• De subquery (SELECT AVG(salary) FROM employees) draait eerst en berekent het gemiddelde salaris.

• De buitenste query haalt vervolgens de werknemers op wier salaris hoger is dan dit gemiddelde.

-- Retrieves names of employees with above-average salary
SELECT employee_name  
FROM employees
-- Subquery: calculates average salary across all employees
WHERE salary > (SELECT AVG(salary) FROM employees);  

Ik raad je aan om DataCamp’s cursus Introduction to SQL Server te volgen om meer te leren over groeperen, gegevensaggregatie en tabellen joinen.

Gecorreleerde subqueries

Gecorreleerde subqueries zijn afhankelijk van de buitenste query voor een deel van hun gegevens en worden daarom voor elke rij die door de buitenste query wordt verwerkt opnieuw geëvalueerd. 

De volgende query wordt in deze volgorde uitgevoerd:

• Voor elke rij in employees (met alias e1) berekent de subquery (SELECT AVG(e2.salary) FROM employees e2 WHERE e2.department_id = e1.department_id) het gemiddelde salaris voor die specifieke afdeling.

• De buitenste query vergelijkt vervolgens het salaris van elke werknemer met het afdelingsgemiddelde en neemt alleen degenen op die meer verdienen.

-- Retrieves names of employees with above-average salary in their department
SELECT e1.employee_name  
FROM employees e1
 -- Subquery: calculates average salary for each department
WHERE e1.salary > (SELECT AVG(e2.salary) FROM employees e2 WHERE e2.department_id = e1.department_id); 

Toepassingen van de SQL-subquery

SQL-subqueries zijn belangrijk voor gegevensopvraging en -transformatie, waardoor je complexe queries kunt schrijven en geavanceerde data-analysetaken kunt uitvoeren. Hieronder staan realistische toepassingen van subqueries in databasebeheer.

Gegevens filteren

Subqueries zijn handig bij het filteren van gegevens op basis van dynamische voorwaarden, vooral wanneer filtering vereist dat waarden over meerdere tabellen worden vergeleken of berekeningen worden uitgevoerd.

De volgende subquery haalt de category_id op van "Product A", en de hoofdquery vindt alle producten in die categorie.

-- Retrieves names of products in the same category as 'Product A'
SELECT product_name  
FROM products
 -- Subquery: finds category ID of 'Product A'
WHERE category_id = (SELECT category_id FROM products WHERE product_name = 'Product A'); 

Gegevensaggregatie

Subqueries worden ook gebruikt voor gegevensaggregatie, vooral bij het genereren van samenvattende statistieken of inzichten voor rapportage en analyse. De subquery (SELECT department_id, AVG(sales) AS avg_sales FROM sales GROUP BY department_id) berekent de gemiddelde verkoop per afdeling. De buitenste query filtert vervolgens afdelingen met een gemiddelde verkoop boven 50.000. 

-- Retrieves department IDs and their average sales
-- Filters for departments with average sales over 50,000
SELECT department_id, avg_sales  
FROM (SELECT department_id, AVG(sales) AS avg_sales FROM sales GROUP BY department_id) AS dept_sales  -- Subquery: calculates average sales per department
WHERE avg_sales > 50000;  

Prestatie-overwegingen en best practices

Hoewel subqueries krachtig zijn bij het schrijven van complexe queries, kunnen ze de prestaties beïnvloeden, vooral bij grote datasets. Het is belangrijk om rekening te houden met veelvoorkomende valkuilen en best practices voor betere performance.

Subquery-prestaties optimaliseren

Het optimaliseren van subquery-prestaties zorgt voor snellere uitvoeringstijden en een responsievere database. Hieronder vind je manieren om de subquery te optimaliseren.

• Indexeer relevante kolommen: Zorg ervoor dat kolommen die in WHERE- en JOIN-clausules en vergelijkingsbewerkingen worden gebruikt, zijn geïndexeerd om gegevensopvraging te versnellen.

• Beperk het gebruik van gecorreleerde subqueries: Gebruik waar mogelijk JOIN-bewerkingen of CTE’s in plaats van gecorreleerde subqueries, omdat deze vaak sneller gegevens verwerken via set-bewerkingen dan via rij-voor-rij-verwerking.

• Beperk het aantal kolommen in subqueries: Selecteer in subqueries alleen de kolommen die je nodig hebt om gegevensopvraging te minimaliseren, het geheugenverbruik te verminderen en de database in staat te stellen de uitvoering te optimaliseren.

• Gebruik EXISTS in plaats van IN: Als een subquery een grote dataset retourneert, kan EXISTS in plaats van IN de prestaties verbeteren. De operator EXISTS stopt de queryverwerking zodra een overeenkomende rij is gevonden, terwijl de operator IN het volledige subquery-resultaat blijft evalueren.

Veelvoorkomende valkuilen vermijden

Als je subqueries onjuist schrijft, kun je problemen tegenkomen bij de uitvoering. Laten we kijken hoe je deze valkuilen kunt vermijden.

• Vermijd onnodige gecorreleerde subqueries: Gecorreleerde subqueries zijn intensief in middelen, dus vermijd ze wanneer het resultaat ook met een niet-gecorreleerde subquery of een join kan worden bereikt.

• Wees alert op NULL-waarden in subqueries: NULL-waarden kunnen tot onverwachte resultaten leiden, vooral in subqueries die vergelijkingsoperatoren zoals IN of = gebruiken. Overweeg om COALESCE te gebruiken om nulls af te handelen of ervoor te zorgen dat kolommen niet nullbaar zijn als ze voor vergelijkingen worden gebruikt, om fouten te vermijden.

• Gebruik geen SELECT * in subqueries: Het gebruik van SELECT * kan inefficiënt zijn, omdat hiermee alle kolommen worden opgehaald, ook als je die niet nodig hebt. Dit verhoogt het geheugenverbruik en kan de uitvoering van de query vertragen, vooral bij grote datasets.

• Gebruik betekenisvolle aliassen: Geef je tabellen en subqueries duidelijke namen om de leesbaarheid te vergroten.

Probeer onze SQL Server Developer career track, waarmee je leert hoe je queries schrijft, troubleshoott en optimaliseert met SQL Server. 

Meer geavanceerde SQL-subquerytechnieken

Hoewel subqueries een efficiënte manier bieden om complexe queries te schrijven, bestaan er geavanceerde methoden voor het verwerken van hiërarchische gegevens. Laten we de geavanceerde technieken en strategieën voor het toepassen van SQL-subqueries bekijken.

Recursieve subqueries

Recursieve subqueries (ook wel recursieve common table expressions of CTE’s genoemd) stellen je in staat hiërarchische gegevens op te halen, zoals organisatiestructuren, productcategorieën of graafgebaseerde relaties, waarbij elk item in de gegevens aan een ander is gekoppeld.

Stel, je hebt een tabel employees met employee_id, manager_id en employee_name. Je wilt de hiërarchie van werknemers onder een specifieke manager ophalen.

WITH RECURSIVE EmployeeHierarchy AS (
    -- Anchor Query: Start with the specified manager
    SELECT employee_id, manager_id, employee_name, 1 AS level
    FROM employees
 -- Assuming the top-level manager has NULL as manager_id
    WHERE manager_id IS NULL 
    
    UNION ALL
    
    -- Recursive Query: Find employees who report to those in the previous level
    SELECT e.employee_id, e.manager_id, e.employee_name, eh.level + 1
    FROM employees e
    INNER JOIN EmployeeHierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM EmployeeHierarchy;

In de bovenstaande query:

• Wat ik de Anchor Query noem, selecteert de topmanager (waar manager_id NULL is).

• De Recursieve Query voegt employees samen met de CTE zelf (EmployeeHierarchy) en vindt werknemers die rapporteren aan elke eerder opgehaalde werknemer.

• De recursie gaat door totdat er geen werknemers meer rapporteren aan de gevonden personen.

Subqueries combineren met andere SQL-functionaliteit

Je kunt subqueries integreren met andere SQL-functies zoals windowfuncties, CASE-expressies en groeperingsfuncties. Deze combinaties maken geavanceerde gegevensmanipulatie en uitgebreidere rapportage mogelijk.

Subqueries combineren met windowfuncties

Subqueries kunnen worden gebruikt om de dataset te verfijnen waarop windowfuncties werken, wat ze nuttig maakt voor ranking, cumulatieve totalen en voortschrijdende gemiddelden. Stel dat je producten per regio op verkoop wilt rangschikken. Je kunt een subquery gebruiken om de relevante gegevens te selecteren en vervolgens een windowfunctie toepassen voor de ranking.

 -- Ranks products by sales within each region
SELECT region, product_id, sales, 
       RANK() OVER (PARTITION BY region ORDER BY sales DESC) AS sales_rank 
-- Subquery: calculates total sales per product in each region
FROM (SELECT region, product_id, SUM(sales) AS sales  
      FROM sales_data
      GROUP BY region, product_id) AS regional_sales;

Subqueries gebruiken met CASE-expressies

Door subqueries te combineren met CASE-expressies kun je complexe voorwaarden toepassen op basis van dynamische berekeningen. De volgende query classificeert producten als “High”, “Medium” of “Low” presteerders op basis van hun verkoop ten opzichte van de gemiddelde verkoop voor hun categorie.

 -- Categorize above-average sales, average sales, and below-average sales
SELECT product_id, category_id, sales,
       CASE 
           WHEN sales > (SELECT AVG(sales) FROM products WHERE category_id = p.category_id) THEN 'High' 
           WHEN sales = (SELECT AVG(sales) FROM products WHERE category_id = p.category_id) THEN 'Medium' 
           ELSE 'Low'
       END AS performance
FROM products AS p;

Subqueries met aggregatiefuncties voor voorwaardelijke aggregatie

Je kunt ook voorwaardelijke aggregaten berekenen met subqueries binnen aggregatiefuncties. Stel dat je de totale omzet wilt berekenen die alleen is gegenereerd door actieve klanten. In het onderstaande voorbeeld haalt de subquery alle actieve klanten op. De hoofdquery filtert vervolgens bestellingen om alleen die van actieve klanten op te nemen en berekent de totale omzet van deze groep.

 -- Calculates total revenue from active customers
SELECT SUM(order_total) AS active_customer_revenue 
FROM orders
  -- Subquery: retrieves IDs of active customers
WHERE customer_id IN (
    SELECT customer_id
    FROM customers
    WHERE status = 'Active'
);

Wanneer SQL-subqueries worden gebruikt

SQL-subqueries bieden veelzijdige, realistische toepassingen voor data-analyse en worden daarom zo vaak gebruikt. Hieronder staan enkele sectorspecifieke of wiskundige toepassingen van SQL-subqueries. Om op deze ideeën te komen, dacht ik aan data-analyse- of data-engineeringtaken die meerdere stappen vereisen; daar helpen subqueries naar mijn mening echt.

Sectorspecifieke voorbeelden

Subqueries kunnen nuttige oplossingen bieden in de financiële sector, de zorg en de retail. Enkele ideeën:

• Risicobeoordeling voor leninggoedkeuringen (financiën): Ik stel me voor dat banken jongleren met metrics zoals schuld-inkomensverhoudingen en kredietscores. Door deze metrics in subqueries te nesten, kunnen analisten beter vat krijgen op complexe financiële maatstaven. Een subquery kan bijvoorbeeld het gemiddelde leenbedrag berekenen voor klanten binnen specifieke inkomensschalen.

• Patronen in patiëntdiagnoses identificeren (zorg): In de zorg kan het beheren van patiëntgegevens veel werk zijn. Subqueries kunnen helpen deze complexiteit op te delen. Ik kan me voorstellen dat subqueries worden gebruikt om diagnosefrequenties te volgen over verschillende leeftijdsgroepen of risicofactoren.

• Productplaatsing optimaliseren op basis van koopgeschiedenis (retail): Retailers floreren door koopgedrag te begrijpen. Subqueries kunnen geneste aankoopgegevens tonen om te identificeren welke producten vaak samen worden gekocht. Dit kan helpen bij het strategisch plaatsen van complementaire artikelen en het verhogen van de verkoop.

Wiskundige verbanden

Subqueries worden ook gebruikt om patronen en trends in gegevens te identificeren in wiskundige en logische verbanden. Hieronder enkele scenario’s waarin subqueries in de wiskunde worden toegepast.

• Voortschrijdende gemiddelden voor tijdreeksanalyse: Bij het analyseren van trends in de tijd vereenvoudigen subqueries het berekenen van voortschrijdende gemiddelden. Ik zie ze specifieke tijdvensters definiëren binnen geneste queries, waardoor het eenvoudiger wordt om data te vergladden en trends te ontdekken.

• Uitschieters detecteren met standaarddeviaties: Uitschieters detecteren is belangrijk voor van alles, waaronder fraudeopsporing. Subqueries maken het eenvoudig om berekende statistieken zoals standaarddeviaties binnen geneste queries te berekenen.

• Concepten uit de verzamelingenleer gebruiken: Ik vind het interessant hoe subqueries bewerkingen uit de verzamelingenleer zoals UNION en INTERSECT weerspiegelen. Deze mogelijkheid is perfect voor taken zoals klantretentieanalyse, waarbij inzicht in overlappen en verschillen tussen klantgroepen kan leiden tot slimmere marketingstrategieën.

Conclusie

Het onder de knie krijgen van SQL-subqueries kan je vermogen om gegevens efficiënt te beheren en te analyseren aanzienlijk vergroten. Door hun structuur, toepassingen en best practices te begrijpen, kun je je SQL-queries optimaliseren voor betere prestaties. Bovendien maakt het beheersen van subqueries het schrijven van SQL gewoon makkelijker, dus het is de moeite waard om te leren.

Als je een bekwame data-analist wilt worden, bekijk dan onze Associate Data Analyst in SQL career track om de benodigde vaardigheden te leren. De cursus Reporting in SQL is ook geschikt als je wilt leren hoe je professionele dashboards bouwt met SQL. Tot slot raad ik aan om de SQL Associate Certification te behalen om je beheersing van SQL voor data-analyse aan te tonen en op te vallen tussen andere dataprofessionals.