Re: Null and not equal in Join Condition
From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 08/19/04
- Next message: Hugo Kornelis: "Re: Help with constraint"
- Previous message: John Francisco Williams: "Re: How do I update an NText Column?"
- In reply to: Robert Taylor: "Null and not equal in Join Condition"
- Next in thread: Robert Taylor: "Re: Null and not equal in Join Condition"
- Reply: Robert Taylor: "Re: Null and not equal in Join Condition"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 19 Aug 2004 21:48:29 +0200
On Thu, 19 Aug 2004 12:03:00 -0700, Robert Taylor wrote:
>I know that Null does not equal anything. Does that mean that I cannot
>do a join where column A has values and column B may have Nulls and
>legitimate values like this:
>
>where
>join on table1.column1=table2.column1
>AND
>***table1.columnA != table2.columnB ***
>
>Any thoughts?
>
>Thanks,
>
>Robert
Hi Robert,
What you use above is invalid syntax, regardless of the data containing
NULLS or not.
However, you can write
SELECT some columns
FROM table1
INNER JOIN table2
ON table1.column1 = table2.column1
WHERE table1.columnA != table2.columnB
But you should be aware of how NULLS behave in logical expressions. You
must familiarize yourself with three-valued logic. Unlike the better known
boolean logic, three-valued logic has "unknown" in addition to "true" and
"false". Any comparison to a NULL value will always be "unknown". It makes
sense, actually: is 17 equal to an value that is not available to the
database? of course, you can't know. But neither can you know if 17 is NOT
equal to a value that's not in the database. And you also can't tell if
two values that are not known to a database are equal to each other, so it
actually makes sense that NULL = NULL and NULL <> NULL both evaluate to
unknown, not to true and false respectively.
Three-valued logic also has impact on how the NOT, AND and OR operators
work. But you can easily work that out for yourself: just take three
propositions, one of which you know to be true (eg: I am Robert), one of
which is false (eg: the Earth is flat) and one of which is unknown to you
(eg: President Bush is wearing pink underwear) - at least, I HOPE that one
is unknown to you :-)
You can work out the results of NOT, AND and OR by combining these
propositions with NOT, AND and OR and answering the question yourself: is
it true that the Earth is flat OR President Bush is wearing pink
underwear? Answer: you don't know, so "false" OR "unknown" = "unknown".
The final thing to know is that SQL doesn't give rows the benefit of the
doubt. A row will only be returned if the complete WHERE clause for that
row evaluates to "true" - if it's "false" or "unknown", it doesn't get
into the result.
Conclusion: the above query is correct; if there are no other conditions,
no rows from table2 with columnB equal to NULL will be in the result. If
that is not what you try to achieve, then the query is wrong for your
problem. But that's another issue.
Best, Hugo
-- (Remove _NO_ and _SPAM_ to get my e-mail address)
- Next message: Hugo Kornelis: "Re: Help with constraint"
- Previous message: John Francisco Williams: "Re: How do I update an NText Column?"
- In reply to: Robert Taylor: "Null and not equal in Join Condition"
- Next in thread: Robert Taylor: "Re: Null and not equal in Join Condition"
- Reply: Robert Taylor: "Re: Null and not equal in Join Condition"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
