Guinguette Marais Poitevin

Ik zal nooit een van mijn eerste SQL-opdrachten vergeten als jonge junior ontwikkelaar bij de federale overheid. Ik moest een rapport maken voor een van onze klanten om hen te helpen statistieken te genereren voor het kabinet van de minister. Toen de cijfers in het rapport sterk afweken van hun eigen schattingen, belden ze een vergadering tussen mijn baas en mij. Ik had geen idee waar of dat ik in de fout was gegaan, tot mijn baas uitriep: “Robert, je hebt een Cartesisch Product gecreëerd. Nadat ze klaar was met me uit te schelden in het bijzijn van onze klanten, werd de vergadering verdaagd en ging ik mijn vraag herschrijven. Ik kwam uiteindelijk met wat ze zochten, maar mijn baas liet me er nooit mee wegkomen, en bracht het in elke vergadering daarna ter sprake: “Robert, herinner je die keer dat je een Cartesisch Product maakte… Ongeveer zes maanden later werd ik overgeplaatst naar een andere afdeling. Het is waar dat mijn baas niet echt vergevingsgezind was, maar dit soort fouten kunnen je carrière echt schaden. Vandaag wil ik met jullie delen wat ik in de loop der jaren over Cartesian Products heb geleerd, zodat jullie ze kunnen herkennen en ze voor altijd uit jullie SELECT queries kunnen bannen.

Hoe genereer ik een Cartesian Product

De volgende query haalt gegevens uit twee tabellen zonder enige vorm van filtering. Het weglaten van de WHERE-clausule kan handig zijn in situaties waarin je alle rijen in een tabel wilt zien, maar de volgorde wilt wijzigen of niet-relevante kolommen wilt verbergen:

SELECT name, gender, CONCAT('$', FORMAT(salary, 2)) AS 'Monthly Salary' FROM employees, shops; 

Het probleem hier is dat de query uit meerdere tabellen selecteert. Zonder expliciete tabel joins, krijgen we een soort standaard join, een Cartesian Join (of Cross Join). De naam Cross Join verwijst naar het feit dat het elke rij van de eerste tabel verbindt met elke rij van de tweede tabel. Met andere woorden, Cartesian Joins vertegenwoordigen de som van het aantal kolommen van de invoertabellen plus het product van het aantal rijen van de invoertabellen.

Je kunt in de resultaten zien dat elke rij in de eerste (werknemers) tabel wordt geretourneerd voor elke rij in de tweede (winkels) tabel. Omdat er drie rijen in de tabel winkels zijn, produceert de query drie van elke rij uit de tabel werknemers:

Dat is een heleboel rijen voor twee kleine tabellen. De resultaten groeien exponentieel als er meer rijen en/of tabellen bij betrokken zijn. Vanwege de druk die zo’n query op systeembronnen legt en het feit dat de resulterende dataset veel te veel informatie bevat voor de query-schrijver om te selecteren wat interessant is, worden Cartesian Joins bijna altijd per ongeluk uitgevoerd. Zoals we in mijn eigen waarschuwende verhaal hebben geleerd, is het goed om te weten hoe je er een kunt herkennen voordat je klanten of supervisor bij je terugkomen met vragen over waarom er zoveel dubbele rijen zijn. Inderdaad, de aanwezigheid van veel duplicaten, gecombineerd met een ongewoon grote resultatenset, is een teken dat je misschien met een Cartesian Product te maken hebt.

Hoe filtercriteria een Cartesian Product kunnen maskeren

SELECT statements die een WHERE clause bevatten kunnen gemakkelijk een Cartesian Product verbergen omdat niet alle rijen in duplicaten zullen verschijnen. Hier is een onschuldig genoeg lijkende query waarin iemand vergat een table join op te nemen:

SELECT name, gender, CONCAT('$', FORMAT(salary, 2)) AS 'Monthly Salary' FROM shops, employeesWHERE shops.shop = 'Zurich';

Omdat de winkel niet in de resultatenset voorkomt, zou het gemakkelijk zijn om aan te nemen dat de output juist is. We kunnen echter gemakkelijk verifiëren dat alleen de eerste twee werknemers in de lijst in Zürich werken. De andere drie lijken te zijn ontsnapt aan onze filtercriteria!

Uitvoeren van alle kolommen laat duidelijker zien wat er aan de hand is. Het filter retourneerde inderdaad alleen de winkel in Zürich. Echter, zonder een goede table join produceert de query een record voor elke werknemer, of ze nu wel of niet zijn gekoppeld aan de winkel in Zürich. Dit is logisch als je bedenkt dat de werknemers niet gekoppeld zijn aan een winkel zonder een join. Het shop_id veld in de werknemers tabel heeft dus niets te maken met dat in de winkels tabel. Wat de query zegt is: “Haal alle rijen uit de winkeltabel waarvan de naam overeenkomt met ‘Zürich’ en alle rijen uit de werknemerstabel”:

shop_id	shop	id	shop_id_1	gender	naam	salaris
	Zürich			m	Jon Simpson
	Zürich			f	Barbara Breitenmoser	(NULL)
	Zürich			f	Kirsten Ruegg
	Zürich			m	Ralph Teller
	Zürich			m	Peter Jonson

Zo ook, het beperken van de resultaten uit de werknemer tabel geeft alleen rijen uit die tabel die voldoen aan de criteria en alle rijen uit de andere tabel. Hier is een query die de werknemers filtert op salaris:

SELECT name, gender, CONCAT('$', FORMAT(salary, 2)) AS 'Monthly Salary' FROM shops, employeesWHERE employees.salary > 5500;

Het komt overeen met één rij in de werknemers-tabel, die eenmaal wordt weergegeven voor elke rij van de winkels-tabel:

naam	geslacht	maandelijks salaris
Kirsten Ruegg	f	$5,600.00
Kirsten Ruegg	f	$5.600,00	$5.600,00.00
Kirsten Ruegg	f	$5.600.00

Wederom bevestigt het opnemen van alle kolommen deze veronderstelling:

id	shop_id	gender	naam	salaris	shop_id_1	shop
		f	Kirsten Ruegg			Zürich
		f	Kirsten Ruegg			New York
		f	Kirsten Ruegg			Londen

Nu lijken deze resultaten niet meteen verdacht omdat, afhankelijk van de criteria en welke kolommen worden weergegeven, dubbele waarden geen ongewoon verschijnsel zijn:

De query toont nog steeds een werknemer voor elke rij in de winkeltabel, maar deze keer toont hij een werknemer voor elke rij van de winkeltabel, evenals drie rijen voor de ene overeenkomende werknemer. Met andere woorden, alle vijf werknemers worden weergegeven voor overeenkomende winkels (die in Zurich) en het werknemer record waarvan het inkomen hoger is dan $5500 (Kristen Ruegg) wordt herhaald voor elke rij in de winkels tabel.

Zoals we vandaag zagen, Cartesiaanse Producten hebben niet de neiging om nuttige informatie te verstrekken. De moraal van het verhaal is daarom deze: vermijd Cartesian Joins ten koste van alles, tenzij je een kristalheldere reden hebt om het te doen.

De juiste gegevens verkrijgen met SQL Joins

” Zie alle artikelen van columnist Rob Gravelle