[SQL Server] OUTER JOIN issues...

[whoami]

Stel, je hebt deze 2 tabelllen:

tblPersoon en tblAdres.

In tblPersoon heb je 1 record:
id: 1
naam: 'whoami'

in tblAdres heb je 1 record:
id: 1
persoonid: 1
straat: straat

Nu, als ik deze query uitvoer:

code:

SELECT *
FROM tblPersoon
LEFT JOIN tblAdres ON tblPersoon.id = tblAdres.persoonid
WHERE straat like 'stttt%'

Dan krijg ik 0 records terug

Als ik deze query uitvoer:

code:

SELECT  *
FROM tblPersoon
LEFT JOIN tblAdres ON tblpersoon.id = tblAdres.persoonid AND straat like 'sttttt%'

Dan krijg ik wel een record terug.

Eigenlijk had ik verwacht dat beide queries eenzelfde resultaat gingen teruggeven, maar blijkbaar is het niet zo.
Een verklaring is makkelijk: bij de eerste query wordt er eerst gejoined (left join), en dan worden de rows die niet aan de WHERE voldoen eruit gefiltert.

Bij de 2de query worden de rows uit tbladres gefiltert die niet aan de voorwaarde voldoen, en dan wordt er gejoined, en aangezien het een OUTER JOIN is, wordt er toch één record weergegeven.

Nu mijn vraag: is dit eigenlijk standaard / of juist behavior ? Hoe gaan andere dbms'en hiermee om?

[curry684]

Dit klopt en is logisch gedrag. Het klopt exact wat je zegt, een WHERE-clause wordt pas uitgevoerd na alle joins, waardoor query 1 niets teruggeeft, en in de 2e query mismatcht de JOIN waarop het left resultaat teruggegeven zal worden met NULL als straat.

Ik geef trouwens eerlijk toe dat ik die 2e query ook even 3 keer moest bekijken voordat ik het doorhad

[ Voor 18% gewijzigd door curry684 op 19-01-2005 12:50 ]

[EfBe]

standaard gedrag zoals onze medicijnman Curry al meldt, en het is op alle databases zo die ansi joins ondersteunen.

je hebt bv op Oracle 8i, die geen ansi joins ondersteunt een probleem om de 2e query te formuleren, omdat je daar altijd aan select * from a, b where... vast zit.

[justmental]

EfBe schreef op donderdag 20 januari 2005 @ 08:40:
standaard gedrag zoals onze medicijnman Curry al meldt, en het is op alle databases zo die ansi joins ondersteunen.

je hebt bv op Oracle 8i, die geen ansi joins ondersteunt een probleem om de 2e query te formuleren, omdat je daar altijd aan select * from a, b where... vast zit.

Dat kan in Oracle 8i ook wel hoor:
1:

code:

SELECT *
FROM tblPersoon
, tblAdres 
WHERE tblPersoon.id = tblAdres.persoonid (+)
AND straat like 'stttt%'

2:

code:

SELECT *
FROM tblPersoon
, tblAdres 
WHERE tblPersoon.id = tblAdres.persoonid (+)
AND straat (+) like 'stttt%'

[EfBe]

Maar dan wordt de where alsnog na de join uitgevoerd volgens mij of heb ik het mis? De join-clause in de ansi join wordt meegenomen tijdens het opbouwen van de resultset waar de where dan rows uit filtert. In de non-ansi join wijze heb je dat niet, dan wordt de where clause dus altijd nadien uitgevoerd. Ik wist wel van die (+) tekens om left/right joins te bewerkstelligen, echter ik weet niet echt of oracle's engine dan die predicates meeneemt in de crossjoin van de tables of niet.

[whoami]

EfBe schreef op donderdag 20 januari 2005 @ 12:14:
Maar dan wordt de where alsnog na de join uitgevoerd volgens mij of heb ik het mis? De join-clause in de ansi join wordt meegenomen tijdens het opbouwen van de resultset waar de where dan rows uit filtert. In de non-ansi join wijze heb je dat niet, dan wordt de where clause dus altijd nadien uitgevoerd. Ik wist wel van die (+) tekens om left/right joins te bewerkstelligen, echter ik weet niet echt of oracle's engine dan die predicates meeneemt in de crossjoin van de tables of niet.

Er staat ook een (+) in de WHERE clausule van die 2de query; met dat zal je dus die functionaliteit bekomen.

[ACM]

't Resultaat is hetzelfde bij de query die justmetal geeft. Alleen de syntax is anders, wel moet je goed letten op de plaatsing van die (+).

En of nou bij het executionplan besloten wordt eerst de tabellen te joinen en dan de niet-voldoenende records er weer uit te filteren of andersom is aan de DB om te bepalen, niet per se aan jou, lijkt me Althans, als het het snelste plan is natuurlijk

whoami schreef op donderdag 20 januari 2005 @ 12:24:
Er staat ook een (+) in de WHERE clausule van die 2de query; met dat zal je dus die functionaliteit bekomen.

Klopt, de (+) zorgt ervoor dat het "optioneel" wordt oid.

[ Voor 26% gewijzigd door ACM op 20-01-2005 12:29 ]

[EfBe]

ACM schreef op donderdag 20 januari 2005 @ 12:28:
't Resultaat is hetzelfde bij de query die justmetal geeft. Alleen de syntax is anders, wel moet je goed letten op de plaatsing van die (+).

En of nou bij het executionplan besloten wordt eerst de tabellen te joinen en dan de niet-voldoenende records er weer uit te filteren of andersom is aan de DB om te bepalen, niet per se aan jou, lijkt me Althans, als het het snelste plan is natuurlijk

Nee dit is niet het geval, de queries zijn functioneel anders, de optimizer kan niet met de volgorde kloten, dan klopt het resultaat niet meer. Daarom is het dus belangrijk wat er werkelijk gebeurt, in welke volgorde: eerst joinen en dan filteren (wat dus inhoudt dat bij het joinen al rows niet zijn meegenomen) of tijdens het joinen al filteren. De filter in een join is per row, de filter in de where is per set.

Daarom is het voor mij wat onduidelijk wat er gebeurt op 8i, want alle predicates staan doorelkaar: zowel de join predicates als de non-join predicates. Nu wordt er een (+) aangegeven voor een left/right join, dat is allemaal leuk, maar houdt dit in dat de query engine alle predicates met een (+) meeneemt in de join? Bij mijn weten doet oracle 8i een cross join en daarna een filter. Daar overnadenkende is dat dus de complete set en zal het werken bedenk ik me nu, maar vaag is het wel (als in: onduidelijk).

[Verwijderd]

Je zou ook de alternatieve notering voor outer joins eens kunnen proberen, dan zet je de conditie in de Where.

SQL:

SELECT 
  *
FROM 
  tblPersoon, tdbAdres
WHERE
  tblPersoon.id *= tblAdres.persoonid AND
  straat like 'stttt%'

[justmental]

EfBe schreef op donderdag 20 januari 2005 @ 15:15:
Daarom is het voor mij wat onduidelijk wat er gebeurt op 8i, want alle predicates staan doorelkaar: zowel de join predicates als de non-join predicates. Nu wordt er een (+) aangegeven voor een left/right join, dat is allemaal leuk, maar houdt dit in dat de query engine alle predicates met een (+) meeneemt in de join? Bij mijn weten doet oracle 8i een cross join en daarna een filter. Daar overnadenkende is dat dus de complete set en zal het werken bedenk ik me nu, maar vaag is het wel (als in: onduidelijk).

1:

code:

SELECT STATEMENT Hint=CHOOSE                       
FILTER
  NESTED LOOPS OUTER
    TABLE ACCESS FULL    TABEL2
      TABLE ACCESS BY INDEX ROWID   TABEL1    
        INDEX RANGE SCAN    INDEX1

2:

code:

SELECT STATEMENT Hint=CHOOSE
NESTED LOOPS OUTER
  TABLE ACCESS FULL  TABEL2
    TABLE ACCESS BY INDEX ROWID TABEL1    
      INDEX RANGE SCAN  INDEX1