[SQL] Join of Subselect; wat wanneer te gebruiken?

[Xanthium]

Beginnersvraag; ik moet de SQL taal onder de knie krijgen in opdracht van mijn opleiding. Door de opdrachten van werkcolleges stap voor stap dmv trail en error te doorlopen begin ik het nu wel door te krijgen. Waar ik alleen niet uitkom is wanneer je het beste een JOIN óf een SUBQUERY moet toepassen. In mijn ogen doen deze functies namelijk hetzelfde. Google weet mij helaas niet het antwoord te verschaffen. Bvd

[Verwijderd]

In een join kun je direct refereren aan een relatie.

join table.col tc on tc.id = othertable.col

Met een subselect kun je een set bieden waarop je je resultaten kunt matchen.

where id in = (select id, amount from othertable group by amount having sale > 1)

Beiden toch heel verschillende implementaties.

[RobertMe]

Met een subselect kun je een set bieden waarop je je resultaten kunt matchen.

where id in = (select id, amount from othertable group by amount having sale > 1)

Dat kan ook met een outer join en IS NOT NULL

Denk dat je beste kan gebruiken wat jouw het beste ligt, tenzij er enorme verschillen in performance/resultaat van EXPLAIN SELECT ... zitten (de ene heeft veel meer stappen nodig om uitgevoerd te worden dan de ander, of eentje doet een full table scan, etc, etc)

[Xanthium]

ah ok bedankt voor de info

[NMe]

Een goeie database engine zal een subselect en een join die hetzelfde doen volgens mij ook hetzelfde execution path geven, trouwens.

[cariolive23]

Xanthium schreef op zondag 25 oktober 2009 @ 16:37:
Waar ik alleen niet uitkom is wanneer je het beste een JOIN óf een SUBQUERY moet toepassen.

Ga met EXPLAIN aan de slag, dan krijg je te zien hoe jouw database (welk merk gebruik je?) de verschillende queries behandelt.

Voor het gebruik van een JOIN heb je een relatie nodig, voor het gebruik van een subquery is dat niet nodig.

Hierboven staan trouwens verschillende foute voorbeelden genoemd, > 1 en IS NOT NULL zijn totaal verschillende vergelijkingen en je kunt niet 2 kolommen in je subselect opvragen en deze gaan vergelijken met 1 kolom in de outer query

[XiniX88]

Subquery als eerste doen... Mits je binnen 1 tabel blijft, altijd een subquery gebruiken. Mooie van joins is dat je tabellen aan elkaar gaat koppelen (1 hele tabel aan 1 of meer andere tabellen) voordat de where clause wordt uitgevoerd.

Mooie van een Subquery is dat je de tabellen niet gaat koppelen, om deze reden EERST de where clause gaat uitvoeren, wat uiteindelijk resulteert in een snellere query met minder geheugen gebruik.

Echter bij joins kan je dan ook weer zoiets doen als:
SELECT * FROM tabel1 AS t LEFT JOIN tabel2 AS s ON (s.id = t.ref_id, s.id IN (2,3,4))

hierdoor geef je in een JOIN al aan dat je alleen de s.id 2 3 of 4 moeten hebben.

Overigens zijn het 2 aparte dingen. Wil je gegevens uit meerdere tabellen, gebruik een JOIN, wil je dat alleen omdat je bij de WHERE clause dingen nodig hebt, gebruik een subquery!

Om het beeld te schetsen:

SELECT <hier extra gegevens nodig? JOIN!> FROM blabla WHERE <hier gegevens nodig van een andere tabel? SUBQUERY!>

[ Voor 7% gewijzigd door XiniX88 op 26-10-2009 09:29 ]

[Woy]

XiniX88 schreef op maandag 26 oktober 2009 @ 09:27:
Subquery als eerste doen... Mits je binnen 1 tabel blijft, altijd een subquery gebruiken. Mooie van joins is dat je tabellen aan elkaar gaat koppelen (1 hele tabel aan 1 of meer andere tabellen) voordat de where clause wordt uitgevoerd.

Mooie van een Subquery is dat je de tabellen niet gaat koppelen, om deze reden EERST de where clause gaat uitvoeren, wat uiteindelijk resulteert in een snellere query met minder geheugen gebruik.

Dat is een gevaarlijke aanname! Je kunt namenlijk helemaal niet goed voorspellen hoe een query uitgevoerd word. Dat word bepaald door het RDBMS. Als die aan de hand van de gegevens ziet dat het beter is om de query op een andere manier uit te voeren, dan zal hij dat gewoon doen.

Je moet in principe gewoon de query nemen die het meest leesbaar is ( En doet wat je wil natuurlijk ). Als je dan tegen performance issues aanloopt ga je met explain kijken waarom die issues optreden, en dan ga je kijken of je het eventueel op kunt lossen door je query op een andere manier te schrijven. Je moet daarbij echter wel opletten dat het resultaat van je query niet veranderd.

[XiniX88]

Woy schreef op maandag 26 oktober 2009 @ 09:42:
[...]

Dat is een gevaarlijke aanname! Je kunt namenlijk helemaal niet goed voorspellen hoe een query uitgevoerd word. Dat word bepaald door het RDBMS. Als die aan de hand van de gegevens ziet dat het beter is om de query op een andere manier uit te voeren, dan zal hij dat gewoon doen.

Je moet in principe gewoon de query nemen die het meest leesbaar is ( En doet wat je wil natuurlijk ). Als je dan tegen performance issues aanloopt ga je met explain kijken waarom die issues optreden, en dan ga je kijken of je het eventueel op kunt lossen door je query op een andere manier te schrijven. Je moet daarbij echter wel opletten dat het resultaat van je query niet veranderd.

Wat ik alleen opnoem is de Oracle basics (niet eens mijn aanname). Tuurlijk zou je altijd explain moeten gebruiken, echter is een JOIN in 9 van de 10 gevallen niet nodig en is een subquery sneller (omdat je zo zelf in de hand hebt wat het DBMS doet). Dit is wat ik probeer aan te geven... Maar dan inderdaad wel met, begin met een subquery als je een andere tabel nodig hebt alleen voor in de WHERE clause.

Het ligt inderdaad aan het specifieke geval... Explain is even gevaarlijk, stel dat je nu een kleine database hebt met 10 regels, wat straks 1 miljoen wordt per tabel, dan zie je ineens dat het wel verschilt... Zorg dan in ieder geval dat je test database goed gevuld is met een hoeveelheid data die je gaat schatten te gebruiken.

[ Voor 6% gewijzigd door XiniX88 op 26-10-2009 10:05 ]

[cariolive23]

Hou ook in de gaten dat er verschillende soorten JOIN's zijn, deze halen verschillende recordsets op. Met de verschillende operators zou je dit ook kunnen bereiken, maar dat is vaak een stuk minder duidelijk en kan dus zorgen voor de nodige bugs. Een leesbare en begrijpelijke query is goud waard, debuggen kost goud geld.

Vergeet niet dat SQL "maar" een taal is om met de database te kletsen, verschillende manieren om een opdracht te geven, kunnen leiden tot dezelfde wijze van uitvoeren en dus ook dezelfde resultaten. Laat de database dit lekker zelf uitzoeken, daar zijn databases tenslotte voor gemaakt. Gebruik EXPLAIN om te zien hoe de parser op een bepaald moment een query uitvoert, dat is de enige manier om hier iets zinnigs over te kunnen zeggen. Het is overigens wel zo dat een volgende keer dezelfde query op een andere manier kan worden uitgevoerd, de database heeft deze vrijheid. Denk bv. aan een totaal verschillend aantal records of andere indexen. Dit maakt optimaliseren op een ontwikkel- of testomgeving vaak ook onmogelijk, de dataset is vaak geen goede afspiegeling van de werkelijkheid. Wat in de ontwikkel- of testomgeving razendsnel gaat, kan in de productieomgeving ineens gruwelijk langzaam zijn.

EXPLAIN, EXPLAIN, EXPLAIN, het kan niet vaak genoeg worden gezegd.

[jbdeiman]

XiniX88 schreef op maandag 26 oktober 2009 @ 09:27:
Subquery als eerste doen... Mits je binnen 1 tabel blijft, altijd een subquery gebruiken. Mooie van joins is dat je tabellen aan elkaar gaat koppelen (1 hele tabel aan 1 of meer andere tabellen) voordat de where clause wordt uitgevoerd.

Gevaarlijke aanname, binnen 1 tabel kan je ook prima een Join gebruiken. Daar is niets mis mee en in sommige gevallen zelfs handiger.
Over het algemeen (MySQL) is de Join sneller (bij koppeling tussen 2 tabellen, of 2x 1 tabel) voor het ophalen van gekoppelde gegevens dan een Subquery. (maar dat is vooral omdat in de subquery vaak al meer wordt opgehaald dan de bedoeling en dat vervolgens weer wordt "weggefilterd")
Eigenlijk ligt de snelheid van een query vooral aan de opbouw en zouden Joins en Subquery's niet heel veel mogen verschillen. Het ligt o.a. aan de interpretatie van de Tool (engine) maar ook zeer sterk aan de opbouw.

Een Join vind ik over het algemeen veel logischer qua opbouw en je ziet ook veel sneller welke gegevens die op moet halen, dan met subquery's.

De enige manier om hier zekerheid over te krijgen bij je query's is testen en explain gebruiken. Vooral dat laatste veel doen, zodat je ook weet wat er (op de achtergrond) gebeurt met je query. Op den duur ga je zelf al heel veel "zien"/ snappen hoe het (ongeveer) gaat en zal je explain minder vaak gebruiken, omdat je al minder tegen prestatie problemen aanloopt.

[ Voor 14% gewijzigd door jbdeiman op 26-10-2009 10:12 ]

[cariolive23]

jbdeiman schreef op maandag 26 oktober 2009 @ 10:11:
Over het algemeen (MySQL)

MySQL is alles behalve algemeen, MySQL doet bijzonder veel dingen die databases helemaal niet horen te doen en MySQL heeft in vergelijking met andere databases maar een zeer beperkte query parser. MySQL zul je dus moeten vertellen hoe een query moet worden uitgevoerd, andere databases doen dit voor jou.

Het is zelfs niet handig om SQL te leren met MySQL, je leert dingen aan die helemaal niet kunnen. Denk aan het optellen van appels en peren, MySQL verzint onzin waar je bijstaat...

Ps. Het gedrag van MySQL is totaal afhankelijk van de configuratie en de versie, wat in de ene config/versie werkt, werkt in de andere ineens totaal anders of zelfs helemaal niet meer.

[jbdeiman]

cariolive23 schreef op maandag 26 oktober 2009 @ 10:26:
[...]

MySQL is alles behalve algemeen, MySQL doet bijzonder veel dingen die databases helemaal niet horen te doen en MySQL heeft in vergelijking met andere databases maar een zeer beperkte query parser. MySQL zul je dus moeten vertellen hoe een query moet worden uitgevoerd, andere databases doen dit voor jou.

Het is zelfs niet handig om SQL te leren met MySQL, je leert dingen aan die helemaal niet kunnen. Denk aan het optellen van appels en peren, MySQL verzint onzin waar je bijstaat...

Ps. Het gedrag van MySQL is totaal afhankelijk van de configuratie en de versie, wat in de ene config/versie werkt, werkt in de andere ineens totaal anders of zelfs helemaal niet meer.

Klopt dat het gedrag van MySQL afhankelijk is van config en versie, ik heb zelf ervaring met Oracle SQL (ong. 2004) en MySQL. -> Op scholen e.d. gebruiken ze vaak MS SQL of Oracle SQL, maar op het internet voert MySQL nog steeds de boventoon. Vandaar mijn opmerking over MySQL.

Met MySQL SQL leren klopt dat dat niet handig is, heb ik ook niet beweerd natuurlijk. Maar omdat MySQL een (nog steeds) veel gebruikte tool is, in de online wereld, moet je daar natuurlijk wel rekening mee houden. Je kan MySQL wel in een strickt modus dwingen, dit maakt een deel van die fouten goed. Maar we dwalen af, het ging om de verschillen tussen Joins en Subquery's.

Wat ik zei over de opbouw van de query's staat bij elke SQL variant, en dat gaat vaak mis/ fout/ omslachtig bij het gebruik van Subquery's.

[XiniX88]

Hahaha, volgensmij blijft dit een eindeloze discussie, ik denk dat ik maar veilig ga zeggen: Maak zelf je keuze tussen de 2 en doe EXPLAIN... Ook al vind ik dit ook geen veilige manier aangezien testdata nooit gelijk is aan live data, en een database kan groeien. En zoals Cariolive23 hierboven zegt, MySQL is helemaal een uitzondering op de regel.

Wat jbdeiman vertelt kan uiteraard ook kloppen, vandaar dat ik zei dat het per situatie verschilt (overigens een echo van "een gevaarlijke aanname" hoeft van mij niet lorre lorre (aldus de papegaai)). Verder blijf ik me aan de (oracle)theorie vasthouden (die zich bij mij meerdere keren heeft bewezen, maar inderdaad bij uitzonderingen ook weer niet)

Zo omslachtig vind ik dit niet:
SELECT * FROM orderregels WHERE orderId IN (SELECT orderId FROM orders WHERE year BETWEEN 2000 AND 2005)

Of
SELECT * FROM orders AS o LEFT JOIN orderregels AS r ON (r.orderId = o.orderId) WHERE o.year BETWEEN 2000 AND 2005

Onderstaande zou (in ieder geval in oracle) trager werken. Heeft iets te maken met de breedte van de kolommen (de 2e query selecteerd meer kolommen dan de eerste) ok * moet je idd wegdenken, en daar gewoon je info neerzetten die je wilt weten.

@jbdeiman (hieronder), ik hoop dat ik dat beeld nu wel heb geschetst, volgensmij in mijn laatste 2 posts ook herhaald dat het situatie afhankelijk is. Echter (persoonlijke keus) begin ik eerst wat volgens de theorie het snelste zou moeten zijn in die situatie. Maar om verder niet een oneindige discussie aan te gaan wil ik het hierbij laten ieder zo zn eigen mening (en uiteraard goed dat je die verdedigd).

@Woy (hieronder) vaak programmeer ik voor grote bedrijven met grote databases, hierbij is het helaas wel nodig te kijken wat het beste presteert, maar hoeft dit alsnog niet tekort te doen aan de leesbaarheid van een query. Zie ook het bovenstaande voorbeeld, beide even leesbaar, maar in theorie is de eerste sneller dan de 2e. Echter is in theorie ook het JOIN syntax voor een DBMS moeilijker dan een plain Query, vandaar dat mij ooit is aangeleerd JOINS te gebruiken als je meer info nodig hebt uit een andere tabel, maar waarbij dit niet van toepassing is een subquery te gebruiken.

[ Voor 55% gewijzigd door XiniX88 op 26-10-2009 11:41 ]

[jbdeiman]

@XiniX88
Met "gevaarlijke aanname" doelde ik ook op de opmerking dat je bij "binnen 1 tabel altijd de subselect moet gebruiken". Dit is niet altijd zo en kan een verkeerd beeld scheppen voor de TS. Wat ik ermee wil zeggen is dat de Subselect dan niet altijd de beste optie is, "altijd" gebruiken is altijd lastig in dit soort dingen, er zijn zoveel situaties mogelijk/ denkbaar dat dit eigenlijk nooit altijd op 1 manier moet.

Ook in Oracle is het niet altijd zo dat die subquery sneller is dan de join. Hier zit wel degelijk verschil in en daar kom je alleen maar achter door te testen. 1 manier is nu eenmaal niet altijd de snelste, alleen zijn soms de verschillen zo minimaal (zeker bij een goede DB/ Query opbouw) dat het niet loont om dit verder te optimaliseren.

[Woy]

@XiniX88: Waar je echter overheen stapt is dat de performance meestal niet eens een probleem is. En dus kun je je beter eerst richten op een leesbare query, en het optimaliseren aan het RDBMS over laten. Pas als je tegen problemen aanloopt moet je gaan kijken met EXPLAIN waar je winst kunt halen.

Je stelling over het beginnen met met een sub-select, met als redenering dat het sneller zou zijn is dan ook nogal onzinnig IMHO.

Als je zou zeggen: "Begin met een sub-select, want dat is beter leesbaar" dan is het beter te verdedigen.

IMHO moet je dus eerst helemaal niet zo op de performance letten, maar gewoon een zo'n duidelijk mogelijke query ( of queries ) schrijven, en dan kijken of de performance daar acceptabel van is.

[Fiander]

Zo omslachtig vind ik dit niet:
SELECT * FROM orderregels WHERE orderId IN (SELECT orderId FROM orders WHERE year BETWEEN 2000 AND 2005)

Of
SELECT * FROM orders AS o LEFT JOIN orderregels AS r ON (r.orderId = o.orderId) WHERE o.year BETWEEN 2000 AND 2005

Onderstaande zou (in ieder geval in oracle) trager werken. Heeft iets te maken met de breedte van de kolommen (de 2e query selecteerd meer kolommen dan de eerste) ok * moet je idd wegdenken, en daar gewoon je info neerzetten die je wilt weten.

De bovenste query kan ook heel erg veel langzamer worden... Als in : "voor elke afzonderlijke regel in orderregels moet de subquery worden uitgevoerd. " hoewel dit meestal wel correct vertaalt word.
Voor deze query zou EXISTS ook beter werken dan IN. Omdat er dan gestopt word met zoeken zodra aan de voorwaarde voldaan word.

Over het algemeen kun je aanhouden dat joins voor de dbms makkelijker te realiseren zijn. Mits alle indexen ( inc FK index ) goed geplaatst zijn.

Eventueel is er nog een derde variant welke je zou kunnen gebruiken.
SELECT * FROM (SELECT * FROM orders WHERE year BETWEEN 2000 AND 2005) AS o LEFT JOIN orderregels AS r ON (r.orderId = o.orderId)

welke SOMS als voordeel heeft dat je joint over minder regels, maar niet de last van een sub select hebt.

[cariolive23]

XiniX88 schreef op maandag 26 oktober 2009 @ 10:42:
Zo omslachtig vind ik dit niet:
SELECT * FROM orderregels WHERE orderId IN (SELECT orderId FROM orders WHERE year BETWEEN 2000 AND 2005)

Of
SELECT * FROM orders AS o LEFT JOIN orderregels AS r ON (r.orderId = o.orderId) WHERE o.year BETWEEN 2000 AND 2005

Dit zijn twee verschillende queries die verschillende resultaten (kunnen) opleveren. De eerste haal alle orderregels op van orders die tussen 2000 en 2005 zijn geplaatst, de tweede haal alle orders op en eventueel de orderregels. Mocht er geen orderregel zijn, krijg je een NULL "retour".

Nu zul je in de praktijk niet zoveel orders hebben waar geen orderregels voor bestaan, maar wanneer we toch aan het mierenneuken zijn... Een INNER JOIN is hier meer op zijn plaats. Al mag je die ook gewoon schrijven als JOIN.

[XiniX88]

Fiander schreef op maandag 26 oktober 2009 @ 11:35:
[...]


De bovenste query kan ook heel erg veel langzamer worden... Als in : "voor elke afzonderlijke regel in orderregels moet de subquery worden uitgevoerd. " hoewel dit meestal wel correct vertaalt word.
Voor deze query zou EXISTS ook beter werken dan IN. Omdat er dan gestopt word met zoeken zodra aan de voorwaarde voldaan word.

Over het algemeen kun je aanhouden dat joins voor de dbms makkelijker te realiseren zijn. Mits alle indexen ( inc FK index ) goed geplaatst zijn.

Eventueel is er nog een derde variant welke je zou kunnen gebruiken.
SELECT * FROM (SELECT * FROM orders WHERE year BETWEEN 2000 AND 2005) AS o LEFT JOIN orderregels AS r ON (r.orderId = o.orderId)

welke SOMS als voordeel heeft dat je joint over minder regels, maar niet de last van een sub select hebt.

INNER queries moeten als eerst worden uitgevoerd alvorens een OUTER query gedaan wordt. Dit is ook logisch, want de IN() scope krijgt een "lijst" terug met ordernummers, deze worden vervolgens gebruikt voor de OUTER query.

Verder deel ik de mening niet, achter een JOIN zit een complexer stuk code dan een simpele query...

@cariolive23, left had right moeten zijn, zo laat hij alleen de rows zien die matchen, en niet de orders ZONDER orderregels. Verder klopt mijn query wel en zou hij dezelfde resultset moeten geven zover ik zo 1 2 3 zie. Maar ik heb deze query in 2 sec neergezet, en het ging meer om het voorbeeld (natuurlijk is afbranden makkelijk) tussen leesbaarheid (waar die discussie over werd gevoerd).

Over de * had ik namelijk al te kennen gegeven dat er vanuit moet worden gegaan dat alleen resultaten uit orderregels nodig waren (anders spreek ik mn eerdere theorie tegen)

[ Voor 34% gewijzigd door XiniX88 op 26-10-2009 12:07 ]

[Woy]

En bovendien heb je niet zoveel aan die beweringen als je er geen bewijs bij levert, en exact aangeeft bij welk RDBMS ( inclusief versie ) dat is.

Een ander RDBMS kan immers een compleet ander execution plan hebben om hetzelfde probleem op te lossen.

Beweringen als

Voor deze query zou EXISTS ook beter werken dan IN. Omdat er dan gestopt word met zoeken zodra aan de voorwaarde voldaan word.

Zijn ook nogal sterk, aangezien een beetje RDBMS ook bij IN echt wel stopt als er aan de voorwaarde voldaan word. Hij zal hoogstens verder zoeken als hij die resultaten verder in het execution plan nog een keer kan gebruiken.

XiniX88 schreef op maandag 26 oktober 2009 @ 11:50:
[...]
Verder deel ik de mening niet, achter een JOIN zit een complexer stuk code dan een simpele query...

Dat hoeft helemaal niet zo te zijn. Een RDBMS kan best een veel complexer execution plan hebben voor een "Simpele" query. Zonder een exact voorbeeld kun je er gewoon geen gefundeerde uitspraak over doen.

[ Voor 24% gewijzigd door Woy op 26-10-2009 11:54 ]

[cariolive23]

In aanvulling op de reactie van Woy, SQL is de taal om met de database te praten. SQL is NIET een taal om de database tot op bitniveau opdracht te geven bepaalde opdrachten op een door jou vastgestelde manier uit te voeren. De database kan/mag/zal zijn eigen interpretatie geven aan de opdracht die jij geeft en deze opdracht zo efficient mogelijk uitvoeren. Het is reeds gezegd, maar een JOIN kan best worden geparst naar een subselect en een subselect kan best worden geparst naar een JOIN. Laat de database het lekker zelf uitzoeken, daar is 'ie database voor geworden.

Zorg jij maar voor een leesbare en begrijpelijke query, daar heb je vaak veel meer aan.