Hallo

Welkom, Gast. Alsjeblieft inloggen of registreren.
Heb je de activerings-mail niet ontvangen?

Recent

23 gasten, 0 leden

Welkom, Gast. Alsjeblieft inloggen of registreren.
Heb je de activerings-mail niet ontvangen?

19 januari 2022, 10:52:57

Login met gebruikersnaam, wachtwoord en sessielengte

Nieuws

Welkom op het vernieuwde NL Computer Forum!

Auteur Topic: JOIN op tabellen, selectiecriteria in de 'WHERE' of in de 'ON'?  (gelezen 12070 keer)

0 leden en 1 gast bekijken dit topic.

Offline Ronald

  • Forum Manager
  • *****
  • Berichten: 1.853
  • Geslacht: Man
    • NL Computer Forum
JOIN op tabellen, selectiecriteria in de 'WHERE' of in de 'ON'?
« Gepost op: 12 juli 2015, 21:00:21 »
Beste Forumleden,

Bij een JOIN op 2 of meer tabellen kunnen selectiecriteria worden opgenomen in 'ON' of in de 'WHERE'. Bijvoorbeeld:

select L.*, R.*
from L
inner join R
on L.id = R.id
where R.kolom = 'ABC'

of

select L.*, R.*
from L
inner join R
on L.id = R.id
and R.kolom = 'ABC'

Maakt dit in zijn algemeenheid überhaupt iets uit?
Maakt het iets uit als de R-tabel héél groot is en de selectie op kolom = 'ABC' een kleine resultset uit de R-tabel oplevert?
Maakt het nog iets uit als er geen sprake is van een INNER join, maar van een LEFT/RIGHT join?
Maakt het database-platform nog uit, bijv. MS SQL Server of Oracle?

Ik ben benieuwd! ;)

Groeten,

Ronald
Forum Manager NL Computer Forum
Microsoft Certified Solutions Expert (MCSE) - Business Intelligence

Offline Hugo

  • Erelid
  • *****
  • Berichten: 101
  • Geslacht: Man
Re: JOIN op tabellen, selectiecriteria in de 'WHERE' of in de 'ON'?
« Reactie #1 Gepost op: 12 juli 2015, 21:30:32 »
Hoi Ronald,

Voor SQL Server is het antwoord dat het voor een INNER JOIN "in principe" (zie onder) niet uitmaakt. Bij een OUTER JOIN is het anders - hier heeft de locatie van de join invloed op de betekenis.

Bijvoorbeeld:

select L.*, R.*       -- Gebruik nooit SELECT * in productie!
from L
left outer join R
on L.id = R.id
and R.kolom = 'ABC';     -- Altijd statements afsluiten met puntkomma!

In dit geval wordt elke kolom van L gekoppeld aan elke rij in R met dezelfde id en die in kolom de waarde ABC heeft - en als dat meer dan één rij is wordt de rij uit L gedupliceerd (tot zover net als bij inner join). Een rij in L die niet gekoppeld kan worden aan een ABC-rij in R blijft gehandhaafd, met NULL waarden in de R kolommen.

select L.*, R.*       -- Gebruik nooit SELECT * in productie!
from L
left outer join R
on L.id = R.id
where R.kolom = 'ABC';     -- Altijd statements afsluiten met puntkomma!

Nu wordt elke kolom van L gekoppeld aan elke rij in R met dezelfde id, ongeacht de waarde in kolom - en als dat meer dan één rij is wordt de rij uit L gedupliceerd (tot zover net als bij inner join). Een rij in L die niet gekoppeld kan worden aan een ABC-rij in R blijft gehandhaafd, met NULL waarden in de R kolommen. Hierna worden alle rijen uit het resultaat van de join gefilterd op ABC - dus alle rijen die door de outer join gehandhaafd waren worden alsnog verwijderd uit het resultaat.
(Dit is de logische verwerking - de optimizer is slim genoeg om te zien dat dit een nodeloos ingewikkelde manier is om een inner join te schrijven en hem zo uit te voeren).

select L.*, R.*       -- Gebruik nooit SELECT * in productie!
from L
left outer join R
on L.id = R.id
where R.kolom IS NULL;     -- Altijd statements afsluiten met puntkomma!

Lijkt op de vorige, maar het filter is nu NULL. Je krijgt dus alleen de rijen uit L die geen enkele match hadden in R, of die een match hadden in R waarvoor kolom in de R tabel al NULL was. Als deze kolom een NOT NULL restrictie heeft is dit een alternatieve schrijfwijze voor NOT EXISTS. (En in hele oude versies van SQL Server soms sneller, dus sommige oudere SQL Server developers gebruiken deze nog steeds - want waarom zou je ook proberen bij te blijven, toch?)

select L.*, R.*       -- Gebruik nooit SELECT * in productie!
from L
left outer join R
on L.id = R.id
where ( R.kolom IS NULL or R.kolom = 'ABC');     -- Altijd statements afsluiten met puntkomma!

Dit is een versie die ik wel eens in echte code ben tegengekomen. Eerst worden R en L gekoppeld op id, maar L rijen zonder match in R blijven gehandhaafd. Daarna wordt gefilterd op NULL (dus: geen match) of ABC. Je krijgt dan in het resultaat alle L die geen rij in R hebben met een andere waarde dan ABC. Concreet voorbeeld: welke kinderen op het kamp houden van andijvie of hebben geen voorkeurseten opgegeven?

En deze is dus heel anders dan:

select L.*, R.*       -- Gebruik nooit SELECT * in productie!
from L
left outer join R
on L.id = R.id
and ( R.kolom IS NULL or R.kolom = 'ABC');     -- Altijd statements afsluiten met puntkomma!

... want nu krijg je alle kinderen (door de outer join blijft L geheel bestaan), met óf de voorkeur voor andijvie of voor een leeg bord als die voorkeur is opgegeven, of anders zonder informatie over de voorkeur.



"in principe"
De SQL Server optimizer mag de query uitvoeren zoals hij wil. Dus als er (zoals bij inner join) geen verschil is, dan zal je normaal gesproken hetzelfde execution plan krijgen, ongeacht of je dingen in de ON of in de WHERE hebt. Maar als je heel complexe queries krijgt, dan neemt het aantal mogelijkheden voor de optimizer exponentioneel toe, en dan zal de optimizer op een gegeven moment besluiten dat het beter is het "good enough" plan te gebruiken ipv nog twintig minuten te zoeken naar een besparking van 3 seconden. M.a.w., dan worden niet alle mogelijke plannen onderzocht. In zo'n geval kan een wijziging in de query ertoe leiden dat een ander deel van de mogelijke plannen binnnen de tijd bekeken kan worden. Het verband tussen de wijziging in de query en de wijziging in het plan is echter niet te voorspellen, en ik raad dit niet aan als een methode voor performance verbetering - te veel factoren maken dit ook voor de toekomst onvoorspelbaar, en er zijn altijd betere manieren om een probleem in een execution plan te verhelpen.
--
Hugo Kornelis, SQL Server MVP

Offline TdJ

  • Sysop
  • *****
  • Berichten: 217
  • Geslacht: Man
    • Mijn bedrijf
Re: JOIN op tabellen, selectiecriteria in de 'WHERE' of in de 'ON'?
« Reactie #2 Gepost op: 14 juli 2015, 20:07:41 »
Ronald,

Zoals Hugo al aangeeft, afhankelijk van de manier en het gewenste resultaat maakt het niets of alles uit ;-)

Wel is het zo dat ik de ervaring hebt dat SQL Server meestal het meest vergevingsgezind is in dat soort gevallen. Oracle doet al snel wat moeilijker (lees: geeft niet het resultaat dat je verwacht) en SQLBase is echt een drama als het op joins aankomt, iets wat in SQL Server gewoon werkt regelmatig niet of niet hetzelfde ;-(

Als je twijfelt in bijvoorbeeld performance, probeer beiden (een paar keer!) en klok de verschillen...

TdJ

Offline Ronald

  • Forum Manager
  • *****
  • Berichten: 1.853
  • Geslacht: Man
    • NL Computer Forum
Re: JOIN op tabellen, selectiecriteria in de 'WHERE' of in de 'ON'?
« Reactie #3 Gepost op: 26 juli 2015, 13:51:25 »
Hoi Hugo,

Dank je, heel helder uitgelegd zo! :) Begrijp ik je nu goed, en zóu het kunnen dat als ik in een heel lange query (met veel joins op diverse tabellen) wél een verschil kan merken door de beperking in de 'ON' te plaatsen ipv in de 'WHERE'? Ik zit dan met name te denken aan een join met een zeer grote tabel (veel kolommen, miljoenen records). Als de filtering op die grote tabel al vroegtijdig in de query staat, zou SQL Server daar zijn voordeel mee kunnen doen, vermoed ik. Of leest SQL Server query's niet van boven naar beneden? ;)

En hoi Tony,

Oracle doet al snel wat moeilijker (lees: geeft niet het resultaat dat je verwacht)

Ja, dat ken ik! Ik pas in Oracle nogal eens de techniek van het 'stapelen' toe: dus eerst een 1e selectie met output laten bepalen en dan vervolgens dáárop een volgende bewerking toepassen. Dat kan soms aanzienlijk sneller zijn dan als je dat allemaal in 1 grote query zou proppen. :) Nadeel van stapelen is dat ja -als je zo'n query na een half jaar weer eens ziet- soms wel even bezig bent om 'm te ontleden. <g>

Groeten,

Ronald
Forum Manager NL Computer Forum
Microsoft Certified Solutions Expert (MCSE) - Business Intelligence

Offline Hugo

  • Erelid
  • *****
  • Berichten: 101
  • Geslacht: Man
Re: JOIN op tabellen, selectiecriteria in de 'WHERE' of in de 'ON'?
« Reactie #4 Gepost op: 26 juli 2015, 15:34:14 »
Hoi Ronald,

Begrijp ik je nu goed, en zóu het kunnen dat als ik in een heel lange query (met veel joins op diverse tabellen) wél een verschil kan merken door de beperking in de 'ON' te plaatsen ipv in de 'WHERE'?


Ja, dat zou kunnen.


Ik zit dan met name te denken aan een join met een zeer grote tabel (veel kolommen, miljoenen records). Als de filtering op die grote tabel al vroegtijdig in de query staat, zou SQL Server daar zijn voordeel mee kunnen doen, vermoed ik. Of leest SQL Server query's niet van boven naar beneden? ;)

Helaas, zo simpel ligt het niet. De query wordt in eerste instantie omgezet in een zogenaamde "parse tree", een interne representatie in relationele termen. Die ziet er al heel anders uit de de query. En daarna gaat de optimizer hard aan het werk met allerlei tranformaties - totdat op een gegeven moment de beslissing wordt genomen om niet verder te gaan en de query uit te voeren met het tot dan toe beste plan.

Een andere volgorde in de invoer kan tot een ander plan leiden, maar er is absoluut geen voor ons inzichtelijke relatie te leggen tussen wijzigingen in de invoer en effect op het plan.

Groetjes,
Hugo
--
Hugo Kornelis, SQL Server MVP

Offline Ronald

  • Forum Manager
  • *****
  • Berichten: 1.853
  • Geslacht: Man
    • NL Computer Forum
Re: JOIN op tabellen, selectiecriteria in de 'WHERE' of in de 'ON'?
« Reactie #5 Gepost op: 31 augustus 2015, 20:52:41 »
Hoi Hugo,

Dank je voor je verdere toelichting! :) Via een nieuwsbrief kwam ik deze blog nog tegen (de auteur is het helemaal met je eens :)):

http://sqlinthewild.co.za/index.php/2015/08/25/qa-from-the-dba-fundamentals-virtual-chapter/

Groeten,

Ronald
Forum Manager NL Computer Forum
Microsoft Certified Solutions Expert (MCSE) - Business Intelligence