[SQL] De waarde 'NULL'

[bartosiej]

Hallo allemaal,

ik heb allemaal rare dingen over de waarde 'NULL' gehoord, nu heb ik er helaas zelf mee te maken...

ik heb de volgende query:

code:

SELECT * FROM sites AS S LEFT JOIN uitnodigingen AS U ON( S.id = U.siteid )

hier komen 5 resultaten uit..

Als ik er het volgende van maak, komen er 0 resultaten uit:

code:

SELECT * FROM sites AS S LEFT JOIN uitnodigingen AS U ON( S.id = U.siteid ) WHERE U.ip <> '$REMOTE_ADDR'

Dit is heel raar want als ik bij de eerste query kijk, zie ik bij alle resultaten bij het veld ip 'NULL' staan (evenals bij alle andere velden van de gejoinde tabel uitnodigingen).

Hoe zorg ik toch dat ik een werkende query krijg?

alvast bedankt,

bartosiej

[P_de_B]

NULL is altijd ongelijk aan iets anders, ook aan zichzelf.

Om te controleren op NULL moet je niet = gebruiken maar IS

SELECT
FROM
WHERE veld IS NULL

of IS NOT NULL

[bartosiej]

ja dat snap ik, maar hoe selecteer ik alle records uit de tabel, behalve waar het ip gelijk is aan het ip van de gebruiker die de query uitvoert? U.ip in de tabel kan de waarde 'NULL' hebben als er nog geen gegevens zijn over de betreffende uitnodiging als ik dan zoek naar alle records WHERE U.ip <> '$ip' dan krijg ik geen records, terwijl ik die wel hoor te krijgen..

SELECT * FROM sites AS S LEFT JOIN uitnodigingen AS U ON( S.id = U.siteid ) WHERE U.ip IS NOT '$REMOTE_ADDR'

bovenstaande werkt niet (als je dat bedoelde..)

[ Voor 42% gewijzigd door bartosiej op 08-03-2006 16:04 ]

[Voutloos]

Dan gebruik je dus een or in je where clause, spannend hoor.

WHERE U.ip IS NULL OR U.ip <> '$REMOTE_ADDR'.

[P_de_B]

Om te controleren of het IP gelijk is aan het IP van de gebruiker doe je gewoon WHERE u.IP = 'REMOTE_ADDR'.

Als ik je goed begrijp wil je dus alle records waarbij het IP adres ongelijk is aan het IP adres van de gebruiker OF het IP adres NULL is?

Als dat zo is zijn er meerdere oplossingen, je kunt bijvoorbeeld doen:

WHERE u.IP <> 'REMOTE_ADDR' OR u.IP IS NULL

Is dit wel wat je bedoeld?

[whoami]

Doe eens zo:

code:

select * from sites AS s 
left join uitnodigingen AS u on s.id = u.siteid AND u.ip <> 'bliep'

Jouw eerste query werkt niet, omdat die WHERE na de JOIN operatie uitgevoerd wordt.
Die JOIN levert dus een resultset op, en dan ga je daarna die resultset gaan filteren met je WHERE. Aangezien die velden die je wilt checken NULL zijn, zijn ze dus niet gelijk aan jouw filter-waarde. (Ongekend <> 'bliep').

[bartosiej]

Voutloos schreef op woensdag 08 maart 2006 @ 16:06:
Dan gebruik je dus een or in je where clause, spannend hoor.

WHERE U.ip IS NULL OR U.ip <> '$REMOTE_ADDR'.

bedankt, was idd een beetje stom..

dit is uiteindelijk de query geworden (voor degene die nog geinteresseerd zijn):

code:

SELECT * FROM sites AS S
LEFT JOIN uitnodigingen AS U 
ON( S.id = U.siteid ) 
WHERE (U.ip IS NULL OR U.ip <> '$REMOTE_ADDR') 
AND (U.datum IS NULL OR U.datum <> '$date') 
AND (U.userid IS NULL OR S.userid <> U.userid) 
AND (U.userid IS NULL OR U.userid <> ".$_SESSION['user'].") 
AND S.actief = 1 
AND S.uitnodigingen > 0 
AND S.userid <> ".$_SESSION['user']."
ORDER BY S.uitnodigingen DESC LIMIT 0,1

whoami schreef op woensdag 08 maart 2006 @ 16:07:
Doe eens zo:

code:

select * from sites AS s 
left join uitnodigingen AS u on s.id = u.siteid AND u.ip <> 'bliep'

Jouw eerste query werkt niet, omdat die WHERE na de JOIN operatie uitgevoerd wordt.
Die JOIN levert dus een resultset op, en dan ga je daarna die resultset gaan filteren met je WHERE. Aangezien die velden die je wilt checken NULL zijn, zijn ze dus niet gelijk aan jouw filter-waarde. (Ongekend <> 'bliep').

ja, dat was misschien was misschien wel een stuk makkelijker geweest, maarja het is nu ook gelukt.

allemaal in ieder geval heel erg bedankt!!

probleem is opgelost.

[ Voor 7% gewijzigd door bartosiej op 08-03-2006 16:23 ]

[whoami]

Probeer het eens op de manier die ik gezegd heb; die zal normaal sneller zijn ook.

[Verwijderd]

whoami schreef op woensdag 08 maart 2006 @ 16:07:
Doe eens zo:

code:

select * from sites AS s 
left join uitnodigingen AS u on s.id = u.siteid AND u.ip <> 'bliep'

Jouw eerste query werkt niet, omdat die WHERE na de JOIN operatie uitgevoerd wordt.
Die JOIN levert dus een resultset op, en dan ga je daarna die resultset gaan filteren met je WHERE. Aangezien die velden die je wilt checken NULL zijn, zijn ze dus niet gelijk aan jouw filter-waarde. (Ongekend <> 'bliep').

Ik zie het verschil niet (buiten de eventuele performancewinst) Jou query haalt de test u.ip<>'bliep' naar voren, zodat er eerst gefilterd wordt en daarna pas gejoind. Maar het probleem zit hem in de test zelf. Niet zozeer in de plaats in de query.

Een null is het ontbreken van informatie. Een null is dus ongelijk aan alles en kan niet via een normale test vergeleken worden.

Een simpel tabelletje
NULL = NULL ONWAAR
NULL<>NULL ONWAAR
NULL = 'bliep' ONWAAR
NULL<>'bliep' ONWAAR

Dus een NULL kan niet gefilterd worden met =, <, >, <>. Iets wat de TS dus gemerkt heeft

(En voor de slimmerikken)
NOT (NULL <>'bliep') is nog steeds ONWAAR Misschien is ONBEKEND een betere omschrijving, maar het punt is dat elke test faalt en ONBEKEND suggereert dat je soms een positief en soms een negatief resultaat krijgt. Dat is dus niet zo.

[ Voor 16% gewijzigd door Verwijderd op 08-03-2006 17:49 ]

[MSalters]

Probleem is gewoon dat SQL niet snapt hoe tristate logic werkt. Met T=true,F=false en U=unknown:

T and T = T
T and F = F
F and T = F
F and F = F
(tot zover niets nieuws)
T and U = U
F and U = U
U and T = U
U and F = U
Daar is onder wiskundigen en informatici vrij weinig discussie over. Maar zoals gezegd doet SQL het nu eenmaal zo, dus daar zul je maar mee moeten leren leven.

[T-MOB]

MSalters schreef op woensdag 08 maart 2006 @ 22:20:
Probleem is gewoon dat SQL niet snapt hoe tristate logic werkt. Met T=true,F=false en U=unknown: [...]

Wat bedoel je precies? Het rijtje dat je opnoemt is namelijk precies zo geimplementeerd in SQL.

SQL:

SELECT 
 IF(NULL AND FALSE, 1, 0),
 IF(NULL AND TRUE, 1, 0)

Resulteert in 2x 0. NULL is dus niet true en niet false. So far dus volledige compliance met de wiskundigen en informatici, er is een derde staat die onbekendheid voorstelt.

Het wordt interessanter op het vlak dat je niet noemt, de:
U | T -> T
U | F -> U
U = U -> U
Vergelijkingen, maar ook daar lijkt me weinig stof tot discussie over te bestaan (en ook deze vergelijkingen evalueren logisch in SQL).

Blijft het over dat je in een situatie waarin je een waarde of wel, of niet moet weergeven een beslissing moet nemen. Als je als beslissingscriterium neemt dat je alleen iets teruggeeft bij een TRUE dan geef je niets terug als de uitkomst onbekend is. (Itt tot waneer je kiest voor returnen op NOT FALSE). Maar welk van deze twee je ook neemt, het zou zonde zijn van de mogelijkheid die je derde positie (U / NULL) je biedt. Want ondanks dat het onbekend is of twee onbekende uitkomsten gelijk zijn, weet je wel dat ze allebij onbekend zijn. Je kunt dus onbekendheid U' als noodzakelijke voorwaarde voor het onbekend zijn van een waarde U poneren. U impliceert dan U', waardoor je met
U' == U' -> T
De derde staat U kunt inzetten als beslissingscriterium. Dit verhaal komt volledig overeen met de NULL implementatie in SQL en is volgens mij niet anders te zien dan tristate logica

[MSalters]

[/quote]
[quote]

T-MOB schreef op donderdag 09 maart 2006 @ 03:48:
[...]

Wat bedoel je precies? Het rijtje dat je opnoemt is namelijk precies zo geimplementeerd in SQL.

SQL:

SELECT 
 IF(NULL AND FALSE, 1, 0),
 IF(NULL AND TRUE, 1, 0)

Resulteert in 2x 0. NULL is dus niet true en niet false.

Ben ik nou blond?

SQL:

SELECT 
 IF(FALSE AND FALSE, 1, 0),
 IF(FALSE AND TRUE, 1, 0)

levert toch ook twee keer 0? Dus als bewijs dat NULL ongelijk is als FALSE faalt het al, laat staan dat NULL de correcte eigenschappen heeft voor tristate logic. Het correcte resultaat zou natuurlijk twee keer NULL moeten zijn.

Het wordt interessanter op het vlak dat je niet noemt, de:
U | T -> T
U | F -> U

Niet echt, in tristate logic geldt nog steeds dat X OR TRUE = TRUE, X AND FALSE = FALSE,
X AND TRUE = X en tenslotte X OR FALSE = X (voor alle X). Dat precies hetzelfde als met booleaanse algebra.

U = U -> U

Misschien de enige verassing, ja. Alhoewel dat waarschijnlijk volgt uit !U - >U.

[ Voor 24% gewijzigd door MSalters op 09-03-2006 20:39 ]

[T-MOB]

MSalters schreef op donderdag 09 maart 2006 @ 20:32:
Ben ik nou blond?

SQL:

SELECT 
 IF(FALSE AND FALSE, 1, 0),
 IF(FALSE AND TRUE, 1, 0)

levert toch ook twee keer 0? Dus als bewijs dat NULL ongelijk is als FALSE faalt het al, laat staan dat NULL de correcte eigenschappen heeft voor tristate logic. Het correcte resultaat zou natuurlijk twee keer NULL moeten zijn.

Je hebt gelijk dat het geen bewijs is dat NULL ongelijk is aan FALSE. Neemt niet weg dat in SQL het geval is dat NULL ongelijk is aan FALSE. "NULL AND FALSE" zou trouwens naar FALSE moeten evalueren en niet naar NULL, maar dat zeg je hieronder zelf ook.

Niet echt, in tristate logic geldt nog steeds dat X OR TRUE = TRUE, X AND FALSE = FALSE,
X AND TRUE = X en tenslotte X OR FALSE = X (voor alle X). Dat precies hetzelfde als met booleaanse algebra.

Zo werkt het toch ook in SQL:

SQL:

SELECT 
    (NULL AND FALSE),
    (NULL AND TRUE),
    (NULL OR TRUE),
    (NULL OR FALSE)

Levert respectievelijk:
0 (FALSE) - NULL - 1 (TRUE) - NULL

[Paul]

Verwijderd schreef op woensdag 08 maart 2006 @ 17:44:
Een null is het ontbreken van informatie. Een null is dus ongelijk aan alles en kan [knip]

Een simpel tabelletje
[knip]
NULL<>'bliep' ONWAAR

Daar spreek je jezelf tegen
Als NULL ongelijk aan alles zou zijn dan volg uit NULL ongelijk aan 'bliep' dus WAAR.

Ik ben hier ook nog nooit tegenaan gelopen, en zou dus ook verwachten als ik "select bla where bla ongelijk 'iets'" uitvoer ik _alles_ krijg waar bla geen 'iets' is, of er nu wel of niet iets in dat veld staat

[.oisyn]

Paul: nee, NULL staat voor "ongedefinieerd", zoals MSalters en T-MOB mooi laten zien in een discussie waarin ze zichzelf tegenspreken en het eigenlijk heel erg eens met elkaar zijn maar dat niet lijken te zien .

Als iets niet bekend is kan het dus alles zijn, ook 'bliep'. Je weet dus niet of NULL <> 'bliep', want voor hetzelfde geld is die waarde die onbekend is wel 'bliep'. NULL<>'bliep' is dus zowel niet TRUE als niet FALSE, maar NULL.

En aangezien de WHERE alleen die rows selecteert waarvoor de expressie in de where-clausule TRUE is (en niet FALSE of NULL), krijg je dus geen records te zien waarvan de kolom NULL is.

[Paul]

Pff... Dat is mij net een niveau te abstract Lang leve de default-property bij create table

Heeft het ook nog ergens praktisch nut (behalve er moeilijke vragen over verzinnen bij bijvoorbeeld proefwerken op school ) of is dit een gevalletje over-designed?
Ik heb er tot dusver alleen nog maar last van gehad (zij het niet met vergelijkingen maar met impliciete casts van ex-collega's die in jarenoude code TDataSet['fieldname'] hebben gebruikt ipv TDataSet.FieldByName('fieldname').AsString die dan netjes (?) een lege string retourneert (alhoewel dat intern wel weer van voornoemde functie gebruik maakt ) op velden die null mochten zijn).

[T-MOB]

Paul Nieuwkamp schreef op donderdag 09 maart 2006 @ 23:40:
Heeft het ook nog ergens praktisch nut (behalve er moeilijke vragen over verzinnen bij bijvoorbeeld proefwerken op school ) of is dit een gevalletje over-designed?

Het praktisch nut (in SQL) is dat je onderscheid kunt maken tussen afwezige data (NULL) en velden die een "lege" waarde hebben (FALSE).
Stel dat je je muziekcollectie opslaat in een database en voor elk liedje het jaartaal in een integerveld veld hebt. Van een aantal tracks weet je het jaartal niet. Als je een veld gebruikt dat niet NULL mag zijn levert de query naar alle tracks van voor 1980 ook de nummers op waarvan je het jaartal niet weet. Die hebben immers jaartal 0 gekregen (of whatever default je hebt ingesteld).
Voor muzieknummers zal dat niet zoveel uitmaken omdat je zeker weet dat je geen muziek uit het jaar 0 hebt (letterlijk gezien althans). Daar kun je dus een extra conditie voor inzetten. In andere gevallen is dat niet zozeer mogelijk. In onderzoeksgegevens is er bijvoorbeeld een wezenlijk verschil tussen iemand met een inkomen van 0 en iemand die weigert zijn inkomen te vertellen. En ja, dan kun je -1 als default instellen, maar uiteindelijk kom je een situatie tegen waarin elke mogelijke waarde van een veld een betekenis heeft en je toch moet kunnen aangeven dat er niets is ingevuld.

Daarnaast heb je in SQL de mogelijkheid to OUTER JOINS. Hiermee is het mogelijk om tabellen te koppelen waarbij er niet noodzakelijkerwijs corresponderende records in beide tabellen staan. Om dat in goede banen te leiden is het noodzakelijk om een NULL status te hebben. De reden is eigenlijk dezelfde als hierboven, maar het is dat er een wezenlijk verschil is tussen het niet bestaan van een corrsponderende rij en een rij met de default waarden. Zonder NULL status zou het bijzonder omslachtig zijn om productgroepen te selecteren die geen producten bevatten.

.oisyn schreef op donderdag 09 maart 2006 @ 23:27:
Paul: nee, NULL staat voor "ongedefinieerd", zoals MSalters en T-MOB mooi laten zien in een discussie waarin ze zichzelf tegenspreken en het eigenlijk heel erg eens met elkaar zijn maar dat niet lijken te zien .

Dat we het (op dit punt) over heel veel eens zijn denk ik ook. Waar ik benieuwd naar ben is waarom NULL in SQL geen tristate logica zou zijn. Ik kan me namelijk geen andere logische `tristate logica` indenken. En ik vrees dat ik op dit vlak wat aan beta-opleiding - en daarmee jargon - mis om goed duidelijk te maken wat ik bedoel

[Verwijderd]

T-MOB schreef op vrijdag 10 maart 2006 @ 02:29:

[...]

Dat we het (op dit punt) over heel veel eens zijn denk ik ook. Waar ik benieuwd naar ben is waarom NULL in SQL geen tristate logica zou zijn. Ik kan me namelijk geen andere logische `tristate logica` indenken. En ik vrees dat ik op dit vlak wat aan beta-opleiding - en daarmee jargon - mis om goed duidelijk te maken wat ik bedoel

Het punt wat MSAlters volgens mij probeert te maken is dat SQL bij vergelijkingen altijd TRUE of FALSE retourneert. Dus NULL AND FALSE = FALSE.

Dit is wiskundig gezien incorrect. Dit zou moeten zijn NULL AND FALSE = UNKNOWN. Deze derde uitkomst kan SQL niet genereren en op dit punt wijkt SQL dus af van de geaccepteerde norm. Je kan om deze conceptuele fout heen werken via de IS NULL toets, maar wiskundig gezien zijn de antwoorden van de toetsen bij SQL dus niet helemaal "correct". Daar staat tegenover dat het zo wel een stuk makkelijker is voor niet wiskundigen.

[T-MOB]

Verwijderd schreef op vrijdag 10 maart 2006 @ 02:57:
[...]

Het punt wat MSAlters volgens mij probeert te maken is dat SQL bij vergelijkingen altijd TRUE of FALSE retourneert. Dus NULL AND FALSE = FALSE.

Dit is wiskundig gezien incorrect. Dit zou moeten zijn NULL AND FALSE = UNKNOWN. Deze derde uitkomst kan SQL niet genereren en op dit punt wijkt SQL dus af van de geaccepteerde norm.

Die uitkomst wordt wel gegenereerd: als je er letterlijk om vraagt krijg je gewoon NULL terug. Dat in SQL op basis van die uitkomst een rij niet wordt teruggegeven is een kwestie van beslissingcriterium. Je kunt nou eenmaal niet "misschien" een rij returnen. De implementatie van de IS NULL methode geeft alleen maar aan dat de ontwikkelaars ook inzien dat het een arbitrair iets is. Hoe dan ook is de logica die voor de beslissing plaatsvind gewoon correct tristate geimplementeerd.

[.oisyn]

Verwijderd schreef op vrijdag 10 maart 2006 @ 02:57:

Het punt wat MSAlters volgens mij probeert te maken is dat SQL bij vergelijkingen altijd TRUE of FALSE retourneert. Dus NULL AND FALSE = FALSE. Dit is wiskundig gezien incorrect. Dit zou moeten zijn NULL AND FALSE = UNKNOWN

Nee, NULL AND FALSE is gewoon FALSE. Want wat voor waarde je ook invult voor NULL, de expressie zal altijd false opleveren aangezien het tweede operand false is. Maar voor NULL AND TRUE geeft SQL wel NULL terug zoals het hoort, en zoals T-MOB al heeft laten zien in een eerdere post.

De discrepancie zat 'm in het begin van de discussie vooral in de IF(x,y,z) functie, maar daarvoor is gewoon gedefinieerd dat ie y retourneert als x = TRUE, en z 'otherwise'. Idd niet helemaal correct naar mijn mening, maar soit

[ Voor 5% gewijzigd door .oisyn op 10-03-2006 12:29 ]

[MSalters]

Idd niet correct, want IF (x,y,z) is dus iets anders als IF (NOT x, z, y).