[SQL] de OR, de NULL en de outer join

[Milmoor]

Zodra ik met Informix (IDS V7.31.UC6A) een OR in mijn WHERE sectie opneem waarbij ik voorwaarden stel aan een tabel die er met een outer join aangehangen is gebeuren er vreemde zaken rond NULL's.

Een voorbeeld zegt meer dan duizend woorden:

Structuur database:
tabel1:
volg_nr
pers_nr

tabel2:
pers_nr
naam

Voorbeeld database
tabel1:
[(1,1),(2,NULL),(3,2)]

tabel2:
[(1,"Jan"),(2,"Piet")]

Voorbeelden:

Query
SELECT tabel1.volg_nr, tabel1.pers_nr, tabel2.naam
FROM tabel1, OUTER tabel2
WHERE tabel1.pers_nr = tabel2.pers_nr
# alle (logische) combinaties

Uitkomst
[(1,1,"Jan"),(2,NULL,NULL),(3,2,"Piet)]

Query
SELECT tabel1.volg_nr, tabel1.pers_nr, tabel2.naam
FROM tabel1, OUTER tabel2
WHERE tabel1.pers_nr = tabel2.pers_nr
AND (tabel1.persnr IS NOT NULL)
# alle combinaties met een naam

Uitkomst
[(1,1,"Jan"),(3,2,"Piet)]

Query
SELECT tabel1.volg_nr, tabel1.pers_nr, tabel2.naam
FROM tabel1, OUTER tabel2
WHERE tabel1.pers_nr = tabel2.pers_nr
AND (tabel1.pers_nr IS NOT NULL)
AND (tabel2.naam = "Jan")
# iedereen die Jan heet

Uitkomst
[(1,1,"Jan"),(3,2,NULL)]

Query
SELECT tabel1.volg_nr, tabel1.pers_nr, tabel2.naam
FROM tabel1, OUTER tabel2
WHERE tabel1.pers_nr = tabel2.pers_nr
AND
(1=2
OR
(
(tabel1.pers_nr IS NOT NULL)
)
)
# (alle combinaties waarvoor geldt 1=2) + (alle combinaties met een naam)

Uitkomst
[(1,1,"Jan"),(3,2,"Piet)]

Query
SELECT tabel1.volg_nr, tabel1.pers_nr, tabel2.naam
FROM tabel1, OUTER tabel2
WHERE tabel1.pers_nr = tabel2.pers_nr
AND
(1=2
OR
(
(tabel1.pers_nr IS NOT NULL)
AND
(tabel2.naam = "Jan")
)
)
# (alle combinaties waarvoor geld 1=2) + (alle combinaties met een naam die Jan is)

Uitkomst
[(1,1,"Jan"),(2,NULL,NULL),(3,2,NULL)]

Waarom komen de laatste twee entries mee?! Wie het weet mag het zeggen

[edit1]Twee and's toegevoegd die ik vergeten was bij de omzetting naar pseudo code[/edit2]
[edit1]Uitkomst een van de voorbeelden aangepast naar de realiteit[/edit2]

[Goodielover]

Ik mis in de laatste 2 queries een AND voor de (1=2 ...)
Misschien is dat de fout of heb jij het verkeerd overgenomen?
Zo klopt de syntax in ieder geval niet.

[whoami]

Ik weet uit ervaring dat Informix soms wel eens rare kronkels heeft met NULL values, maar of dat relevant is voor uw probleem...

het is al weer een tijd geleden, maar één van de dingen was naar ik geloof:

code:

select * from tab1, outer tab2
where tab1.id = tab2.Fk
AND tab2.naam = 'bla'

dat je dan wel alle records terug kreeg met tab2.naam = 'bla' maar ook die waarvan tab2.naam = NULL.
(Zo iets ongeveer, is alweer een jaar geleden dat ik nog voor Informix geprogged heb)

[Milmoor]

Bedankt, dat was de aanwijzing die ik nodig had. De eigenlijke situatie blijkt als volgt: op het moment dat er een beperkende voorwaarde "vw_2" aan een via een outer join gekoppelde tabel "tb_2" gesteld wordt worden alle volgens de overige beperkende voorwaarden correcte combinaties opgeleverd. Bij combinaties die niet voldoen aan "vw_2" is het alsof de betreffende regel niet in "tb_2" voorkomt; aangezien deze tabel met een outer gekoppeld is worden de entries gesimuleerd met NULL's.
Dit levert onverwachte resultaten op op het moment dat je beperkende voorwaarden over "tb_2" gaat stellen betreffende NULL's. Bijvoorbeeld zeggen dat een specifieke entry in "tb_2" gelijk moet zijn aan 123 en dat hij niet NULL mag zijn kan dus nog steeds resultaten leveren waarbij deze entry (een ivm de outer join gesimuleerde) NULL bevat?!

[Milmoor]

Een foutje in mijn eerste bericht, in alle voorbeelden:
(tabel1.pers_nr IS NOT NULL) moet natuurlijk zijn (tabel2.pers_nr IS NOT NULL)

Ik ben er achter:
De WHERE clausule geeft de beperkende voorwaarden voor de JOIN, de HAVING clausule die van na de JOIN.

Uit de Informix ISQL Syntax:
WHERE: Sets conditions on the selected rows
HAVING: Sets conditions on the summary results

edit:
voorbeelden verwijderd, er zitten net wat meer haken en ogen aan dan ik zo kan overzien