Mathlight Mathlight - 5 months ago 16
SQL Question

SQL Where ID equals ID but can be null

I'm trying to fetch some data from an db. I've got an

Taak
table with an possible
idPartij
column. Possible, because it can be an real
idPartij
, but can also be
null
.

The query I've got:

SELECT T.idTaak,
T.Taaktype,
P.Partijnaam,
T.Naar,
T.UltimatumDatum,
T.Opmerking,
T.Status,
T.Prioriteit
FROM Taak AS T,
Partij AS P
WHERE T.idPartij = P.idPartij
ORDER BY idTaak DESC


This is working fine when I've got an id in
T.idPartij
, but as mentioned earlier, that id can be
null
. And when that is the case, the row won't be in the result.

Only problem: When I remove the where clause, I get the rows a lot ( because the
Partij
table isn't filtered anymore...

I'm an total noob at SQL, so i can't think of an way to "fix" this problem. Only thing i can think of is creating 2 query's, but i don't think that is an nice way to do...

Answer

Since you are trying to link two tables, you should use a LEFT OUTER JOIN instead:

SELECT T.idTaak,
       T.Taaktype,
       P.Partijnaam,
       T.Naar,
       T.UltimatumDatum,
       T.Opmerking,
       T.Status,
       T.Prioriteit
FROM Taak AS T LEFT OUTER JOIN Partij AS P
   ON T.idPartij = P.idPartij
ORDER BY idTaak DESC

If you don't want to include the NULL values use an INNER JOIN instead.

Note that you should not use these old-style-joins it's a bad habit to kick.


According to the NULL issue:

You cannot use = NULL or <> NULL because NULL is not equal or unequal to anything. NULL means unknown.

So use IS NULL or IS NOT NULL:

WHERE T.idPartij IS NULL OR T.idPartij = P.idPartij

From MSDN

A value of NULL indicates that the value is unknown. A value of NULL is different from an empty or zero value. No two null values are equal. Comparisons between two null values, or between a NULL and any other value, return unknown because the value of each NULL is unknown.