Cartesisch product in SQL: een complete gids

Het cartesisch product is een fundamenteel concept in SQL dat zowel krachtig als complex kan zijn. Als je weet hoe je het effectief inzet, vergroot dat je mogelijkheden om data te manipuleren aanzienlijk. In deze gids duiken we in de ins en outs van cartesische producten, met praktische inzichten en voorbeelden. 

Ben je nieuw met SQL? Begin dan met onze cursus Intermediate SQL om een stevige basis op te bouwen. Ook het SQL Basics Cheat Sheet, dat je kunt downloaden, is een handige referentie met de meest gebruikte SQL-functies.

Wat is een cartesisch product?

Het cartesisch product in SQL is het resultaat van het combineren van elke rij uit de ene tabel met elke rij uit een andere tabel. Deze operatie ontstaat meestal via een CROSS JOIN, wat een resultset vormt met alle mogelijke paren rijen uit de twee tabellen. Hoewel krachtig, kan dit leiden tot zeer grote resultsets, zeker bij tabellen met veel rijen, dus gebruik het met beleid. Sterker nog, veel developers ontdekken cartesische producten waarschijnlijk op de harde manier: per ongeluk een join-voorwaarde weglaten en eindigen met een gigantische resultset.

Wiskundige achtergrond

In de wiskunde is een cartesisch product (genoteerd als A × B) de verzameling van alle mogelijke geordende paren die ontstaan door elk element uit verzameling A te koppelen aan elk element uit verzameling B. In SQL vertaalt dit zich naar het koppelen van elke rij uit de ene tabel aan elke rij in een andere.

Voorbeeld van een cartesisch product. Afbeelding door de auteur.

SQL cross join

De cross join-operatie in SQL creëert een cartesisch product tussen twee tabellen door elke rij uit de eerste tabel te verbinden met elke rij uit de tweede. Zo genereert de cross join alle mogelijke rijcombinaties voor de data. Laten we een voorbeeld bekijken van een cross join in SQL.

Stel dat je twee tabellen hebt, Students en Courses:

Tabel Students

Tabel Courses

In de volgende query wordt elke student gekoppeld aan elk vak, met hun leerjaar en moeilijkheidsniveaus.

-- Perform the cross join to list each student with every course
SELECT Students.name, Students.grade, Courses.course, Courses.level
FROM Students
CROSS JOIN Courses;Mogelijk gemaakt door

Voorbeeld van een cartesisch product in SQL als resultaat van een cross join. Afbeelding door de auteur.

Wanneer wel en niet het cartesisch product in SQL gebruiken

Het cartesisch product in SQL kan erg nuttig zijn wanneer je het gedrag begrijpt en het bewust inzet. Laten we kijken naar de use-cases (en mogelijke valkuilen) van het gebruik van het cartesisch product.

Use-cases

De volgende scenario's zijn voorbeelden waarin een cartesisch product nuttig kan zijn:

• Combinaties genereren: Een cartesisch product is ideaal om alle combinaties van twee verzamelingen waarden te maken. Zo kun je bijvoorbeeld producten koppelen aan kortingen, medewerkers aan dienstenroosters, of alle mogelijke koppelingen tussen verschillende categorieën creëren.
• Testsituaties: Bij het uitvoeren van testcases kan een cartesisch product helpen om elk mogelijk scenario te simuleren door verschillende parametersets of configuraties te combineren.
• Ontbrekende data aanvullen: Een cartesisch product kan ook helpen hiaten te identificeren door alle mogelijke combinaties te genereren en vervolgens met een left join de resultaten te vergelijken om ontbrekende records te vinden, zoals ontbrekende datums in een tijdreeksanalyse.

Mogelijke valkuilen

Hoewel het cartesisch product nuttig is in sommige situaties, zijn er risico's, waaronder het volgende:

• Prestatieproblemen: Omdat een cartesisch product de rijen van de ene tabel vermenigvuldigt met die van een andere, kan de uitkomst exponentieel groeien. Als de dataset te groot is, kan dit leiden tot trage query-uitvoertijden, overmatig geheugengebruik en zelfs databasecrashes.
• Onbedoelde dataduplicatie: Zonder zorgvuldige filtering kunnen cartesische producten rijen opleveren die dubbel lijken of irrelevant zijn voor de analyse, wat kan leiden tot scheve interpretaties of onjuiste resultaten als de duplicaten niet verwacht of correct afgehandeld worden.
• Moeilijk te debuggen: Onbedoelde cartesische producten, zoals het vergeten van een join-voorwaarde, kunnen leiden tot een cross join in plaats van een inner of outer join. Dit kan onverwachte output opleveren die lastig te herleiden en te corrigeren is.

Praktische toepassingen van het cartesisch product

Hoewel het cartesisch product vanwege de potentiële risico's spaarzaam wordt gebruikt, is het nuttig in scenario's waarin je elke mogelijke paarvorming van data-elementen nodig hebt.

Datacombinaties

Het cartesisch product is handig voor het berekenen van relatieve prestatiemetingen, het analyseren van compatibiliteit of het opzetten van ranglijsten. Wanneer je alle elementen in twee datasets met elkaar moet vergelijken, kun je met het cartesisch product elk item koppelen aan elk ander item. 

Wil je bijvoorbeeld de prestaties van elke medewerker vergelijken met die van elke andere medewerker, dan creëert het cartesisch product alle mogelijke medewerkersparen:

-- Select two columns with employee names from the Employees table
SELECT 
    A.employee_name AS Employee1,
    B.employee_name AS Employee2
FROM Employees AS A
-- Cross join to create all possible pairs of employees
CROSS JOIN Employees AS B;Mogelijk gemaakt door

Een cartesisch product is ook belangrijk in e-commerceanalyses om het volledige scala aan product-kortingcombinaties te verkennen. Stel dat je tabellen Products en Discounts hebt; met een cross join kun je een lijst maken van alle product-kortingcombinaties:

-- Select product names and discount types
SELECT 
    Products.product_name,
    Discounts.discount_type
FROM Products
-- Generate all product and discount combinations
CROSS JOIN Discounts;Mogelijk gemaakt door

Testen en validatie

Een cartesisch product maakt het mogelijk om alle potentiële parametercombinaties in testomgevingen te creëren, wat essentieel is voor uitgebreide scenariotests. Als je bijvoorbeeld een systeem test met verschillende OS-, apparaat- en browserconfiguraties, zorgt een cross join ervoor dat elke mogelijke configuratie wordt getest.

-- Select combinations of operating systems, devices, and browsers
SELECT 
    OS.name AS OperatingSystem,
    Device.name AS Device,
    Browser.name AS Browser
FROM OperatingSystems AS OS
-- All combinations with devices
CROSS JOIN Devices AS Device
-- All combinations with browsers           
CROSS JOIN Browsers AS Browser;Mogelijk gemaakt door

Een cartesisch product kan helpen ontbrekende records te identificeren, wat nuttig is om datacompleetheid te waarborgen. Als je bijvoorbeeld dagelijkse verkoopdata analyseert en wilt bevestigen dat er voor elk product elke dag data is, kun je een cartesisch product maken van de tabellen Products en Dates. Vervolgens kun je de daadwerkelijke verkoopdata met een left join koppelen om ontbrekende datum-productcombinaties te vinden.

-- Select dates and products with no sales
SELECT 
    Dates.date,
    Products.product_name,
    Sales.sales_amount
FROM Dates
-- Create all date and product combinations
CROSS JOIN Products                          
-- Match sales by date and product
LEFT JOIN 
    Sales ON Dates.date = Sales.date 
           AND Products.product_name = Sales.product_name  
-- Filter for combinations with no sales
WHERE Sales.sales_amount IS NULL;Mogelijk gemaakt door

Enkele ideeën over performance

Omdat cartesische producten mogelijke datacombinaties tussen twee tabellen genereren, kunnen er flinke prestatieproblemen ontstaan bij grote tabellen. Ik raad de volgende technieken aan voor query-optimalisatie en het omgaan met grote datasets om de databaseprestaties te verbeteren.

Query-optimalisatie

Overweeg de volgende methoden om efficiënte queries te schrijven die cartesische producten bevatten.

• Beperk het aantal rijen vóór het joinen: Verminder het aantal rijen in het cartesisch product door filters toe te passen op elke tabel voordat je de cross join uitvoert. Gebruik bijvoorbeeld WHERE-clausules om elke tabel op relevante criteria te filteren en zo de resulterende dataset te minimaliseren.

• Gebruik SELECT met specifieke kolommen: Geef bij een cartesisch product alleen de kolommen op die je nodig hebt in de SELECT-clausule om het geheugengebruik te verminderen en de performance te verbeteren. Vermijd SELECT *, want dat geeft alle kolommen terug en kan tot grote, onhandige resultaten leiden.

• Voorwaarden toepassen om cross joins te beperken tot inner joins: Als alleen bepaalde rijcombinaties relevant zijn, voeg dan extra voorwaarden toe aan de cross join die werken als een INNER JOIN. Zo beperk je de output door te specificeren welke rijen uit elke tabel aan elkaar gekoppeld moeten worden.

• Batchverwerking: Splits voor grote datasets de queries op in behapbare delen voor batchverwerking. Dit kan geheugenproblemen voorkomen en de responstijd verbeteren, vooral bij queries op grote tabellen.

Grote datasets beheren

Gebruik de volgende technieken om de grote datasets te beheersen die uit cartesische producten voortkomen.

• Indexeren: Voeg indexen toe op vaak gejoinde kolommen om de performance te verbeteren bij het filteren of koppelen van grote tabellen. Hoewel indexering cross joins niet direct versnelt, is het nuttig wanneer je later filtert of het cartesisch product met andere tabellen joint, zodat de database rijen sneller kan benaderen.

• Grote tabellen partitioneren: Partitioneren kan de prestaties verbeteren van queries met cartesische producten. Door tabellen op te delen in kleinere, beter beheersbare delen op basis van bepaalde kolommen, kan de database tijdens uitvoering alleen de relevante partities lezen.

• Beperk het aantal resultaatrijen: Gebruik de LIMIT-clausule om het aantal geretourneerde rijen te beperken als je alleen een steekproef uit het cartesisch product nodig hebt voor testen of verificatie.

• Tijdelijke tabellen voor tussenresultaten: Sla tussenresultaten op in tijdelijke tabellen als je een cartesisch product meerdere keren moet hergebruiken. Zo vermijd je herberekeningen en versnel je vervolgoperaties.

Andere SQL-bewerkingen die helpen

In situaties waarin het genereren van een volledig cartesisch product niet efficiënt is, bestaan er alternatieven. Met de onderstaande methoden bereik je je doel zonder de database te overladen.

Inner en outer joins

Een INNER JOIN retourneert alleen rijen met een match tussen de opgegeven kolommen in beide tabellen. Deze techniek beperkt het resultaat tot relevante combinaties en voorkomt de veelheid aan rijen die bij een cartesisch product hoort. Een inner join is geschikt wanneer je alleen de rijen nodig hebt die aan specifieke criteria in beide tabellen voldoen. 

In het onderstaande voorbeeld geeft de INNER JOIN alleen producten en kortingen terug die dezelfde categorie delen.

-- Select products and corresponding discounts by category
SELECT 
    A.product_name,
    B.discount_type
FROM Products AS A
-- Match products and discounts by category
INNER JOIN Discounts AS B ON A.category_id = B.category_id;Mogelijk gemaakt door

De outer join (LEFT JOIN, RIGHT JOIN en FULL JOIN) retourneert gematchte en ongematchte rijen uit één of beide tabellen. Dit is handig wanneer je alle rijen uit de ene tabel wilt ophalen en eventuele matches uit de andere, maar zonder alle mogelijke combinaties te creëren. Een outer join is geschikt wanneer je ongematchte data wilt meenemen in je analyse, maar geen volledige cartesische koppeling nodig hebt.

De LEFT JOIN in de onderstaande query retourneert alle producten, inclusief die zonder bijbehorende korting, met NULL-waarden voor ongematchte kortingen.

-- Select products with corresponding discounts, (NULL if no matching discount)
SELECT 
    A.product_name,
    B.discount_type
FROM Products AS A
-- Match products with discounts by category
LEFT JOIN Discounts AS B ON A.category_id = B.category_id;  Mogelijk gemaakt door

Ik raad DataCamps cursus Introduction to SQL Server aan om meer te leren over het joinen van tabellen en het ophalen van data uit meerdere tabellen.

Subquery's en CTE's

Subquery's laten je specifieke data uit één tabel selecteren en die gebruiken om resultaten in de hoofdquery te filteren. Dit helpt onnodige rijcombinaties te vermijden door te filteren op alleen de relevante records. Subquery's zijn nuttig wanneer je data vooraf wilt filteren of specifieke waarden wilt berekenen voor gebruik in de hoofdquery, zonder een volledige cartesische join nodig te hebben.

De subquery filtert kortingen met waarden boven 10 voordat er gejoined wordt, waardoor minder data verwerkt wordt en een onnodige cross join wordt vermeden.

-- Select products with discounts greater than 10 by category
SELECT 
    A.product_name,
    B.discount
FROM Products AS A
-- Subquery to filter discounts over 10
INNER JOIN 
    (SELECT discount, category_id   
     FROM Discounts 
     WHERE discount > 10) AS B
-- Match products with filtered discounts by category
ON A.category_id = B.category_id;   Mogelijk gemaakt door

CTE's, ofwel WITH-clausules, maken het mogelijk om complexe queries op te delen in stappen. Deze methode helpt cartesische producten te vermijden door te filteren of te aggregeren vóór het joinen. CTE's zijn geschikt om meerstapsqueries te vereenvoudigen, vooral wanneer je vooraf aggregeert of meerdere joins uitvoert, maar een volledig cartesisch resultaat wilt vermijden.

De onderstaande query gebruikt een Common Table Expression (CTE) om kortingen groter dan 10 te filteren voordat er op categorie met producten wordt gejoined.

-- Filter discounts and join with products by category
WITH FilteredDiscounts AS (
    SELECT discount, category_id
    FROM Discounts
    WHERE discount > 10
)
SELECT 
    A.product_name,
    FD.discount
FROM Products AS A
-- Join with filtered discounts by category
INNER JOIN 
    FilteredDiscounts AS FD ON A.category_id = FD.category_id;Mogelijk gemaakt door

Innovatieve toepassingen van het cartesisch product

Hoewel het cartesisch product veel wordt gebruikt in basis-SQL-bewerkingen, zijn er ook toepassingen buiten traditionele databasecases. Laten we bekijken hoe het cartesisch product kan worden ingezet bij machine learning-datapreparatie en het genereren van synthetische datasets voor simulaties.

Datapreparatie voor machine learning

Diverse en volledige datasets zijn belangrijk bij het voorbereiden van data voor machine learning om robuuste modellen te trainen. Cartesische producten kunnen een complete dataset creëren die alle mogelijke combinaties van featurewaarden dekt, zodat een model kan leren van een breder scala aan scenario's. 

Bijvoorbeeld: bij het bouwen van fraudedetectiemodellen kun je transactie­types, regio's en apparaattype combineren om verschillende omstandigheden te simuleren, zodat het model generaliseert over uiteenlopende gevallen.

Synthetische data genereren

Synthetische data wordt vaak gebruikt om systemen onder uiteenlopende omstandigheden te testen of om privacy te beschermen door geen echte data te gebruiken. Cartesische producten helpen bij het genereren van diverse synthetische datasets, nuttig voor toepassingen van simulaties tot data-augmentatie voor machine learning-modellen.

Je vindt hieronder een eenvoudig voorbeeld van hoe je, bij wijze van spreken, synthetische data “uit het niets” kunt maken in SQL en vervolgens met een CROSS JOIN kunt uitbreiden tot een grotere dataset. Het idee is dat elke kleine tabel een dimensie van onze testdata voorstelt en dat we, door ze te cross-joinen, alle mogelijke combinaties krijgen.

Voorbeeld 1: Meerdere dimensies combineren

Stel dat je verschillende financiële scenario's wilt simuleren op basis van marktomstandigheden, rentestanden en klantprofielen. We kunnen elke dimensie definiëren via een Common Table Expression (CTE) of tijdelijke tabel met letterlijke waarden. Vervolgens voegt een CROSS JOIN ze samen tot elke mogelijke combinatie:

WITH MarketConditions AS (
    SELECT 'Bull' AS condition
    UNION ALL
    SELECT 'Bear'
    UNION ALL
    SELECT 'Sideways'
),
InterestRates AS (
    SELECT 1.5 AS rate
    UNION ALL
    SELECT 2.0
    UNION ALL
    SELECT 3.25
),
CustomerProfiles AS (
    SELECT 'Younger' AS profile
    UNION ALL
    SELECT 'Mid-career'
    UNION ALL
    SELECT 'Retired'
)
SELECT 
    mc.condition,
    ir.rate,
    cp.profile
FROM 
    MarketConditions mc
CROSS JOIN 
    InterestRates ir
CROSS JOIN 
    CustomerProfiles cp;Mogelijk gemaakt door

De resultset bevat 3×3×3 = 27 rijen. Elke rij is één unieke combinatie van marktomstandigheid, rente en klantprofiel.

Voorbeeld van synthetische datageneratie met behulp van een cartesisch product. Afbeelding door de auteur.

Voorbeeld 2: Rijen genereren met reeksen of eenvoudige VALUES

Een andere techniek is om een reeks getallen of tijdsintervallen te genereren door kleine tabellen met zichzelf te cross-joinen. Dit kun je gebruiken om snel grote hoeveelheden synthetische rijen te produceren. Zo kunnen we met de volgende query de getallen 1 tot en met 10 genereren.

WITH
Nums AS (
    SELECT 1 AS n
    UNION ALL SELECT 2
    UNION ALL SELECT 3
    UNION ALL SELECT 4
    UNION ALL SELECT 5
    UNION ALL SELECT 6
    UNION ALL SELECT 7
    UNION ALL SELECT 8
    UNION ALL SELECT 9
    UNION ALL SELECT 10
)
SELECT n
FROM Nums;Mogelijk gemaakt door

Het cartesisch product gebruiken om rijen te genereren met reeksen. Afbeelding door de auteur.

Vervolgens kunnen we dezelfde set van 10 rijen met zichzelf cross-joinen om 10×10=100 te maken:

WITH
Nums AS (
    SELECT 1 AS n
    UNION ALL SELECT 2
    UNION ALL SELECT 3
    UNION ALL SELECT 4
    UNION ALL SELECT 5
    UNION ALL SELECT 6
    UNION ALL SELECT 7
    UNION ALL SELECT 8
    UNION ALL SELECT 9
    UNION ALL SELECT 10
)
SELECT A.n AS n1, B.n AS n2
FROM Nums A
CROSS JOIN Nums B
ORDER BY A.n, B.n;Mogelijk gemaakt door

Als resultaat krijg je 100 rijen, waarbij elke rij een paar waarden (n1,n2) bevat. Je kunt deze logica uitbreiden naar honderden, duizenden of zelfs miljoenen rijen—maar let op performance en opslag.

Met deze aanpak—CTE's (of VALUES-clausules) voor dimensiedefinities en CROSS JOIN voor combinaties—kun je snel synthetische datasets maken in SQL zonder te vertrouwen op bestaande tabellen.

Conclusie

Inzicht in het cartesisch product in SQL opent een scala aan mogelijkheden voor datamanipulatie en analyse. Door dit concept te beheersen, verbeter je je vermogen om met complexe datasets te werken en je databasequeries te optimaliseren. Voor meer geavanceerde inzichten kun je onze skill track SQL Server Fundamentals volgen om je expertise te verdiepen. 

Afhankelijk van je loopbaanrichting raad ik aan te kiezen voor DataCamps Associate Data Analyst in SQL career track om de vaardigheden op industrieniveau te leren die je nodig hebt om data-analist te worden, of onze Associate Date Engineer in SQL career track om data engineer te worden.