SQL Where ID equals ID but can be null

Question

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...

@GuillemoMansilla, didn't thought of it... – Mathlight Jul 02 '14 at 14:15 — Mathlight, Jul 02 '14 at 14:15

score 5 · Accepted Answer · edited Jul 29 '20 at 18:25

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.

I've copy passed your statement, but i still get an big list with all the `Partij`en with the same `Taak` — Mathlight, Jul 02 '14 at 14:17
@Mathlight: i've overlooked first that you're trying to ink two tables. Use a `LEFT OUTER JOIN` instead. I've edited my answer accordingly. — Tim Schmelter, Jul 02 '14 at 14:20
The left outer join did indeed the trick. Thank you very much! — Mathlight, Jul 02 '14 at 14:23

Patrick Hofman · Answer 2 · 2014-07-02T14:27:52.027

2

You didn't specify a database platform, so it is safe to use ANSI syntax.

Do a coalesce to compare P.idPartij when T.idPartij is null:

where P.idPartij = coalesce(T.idPartij, P.idPartij)

It will match all T rows if T.idPartij is null.

If you want to return no T row when idPartij is null, use a left outer join:

from   Taak t
left
outer
join   Partij p
on     t.idPartij = p.idPartij

edited Jul 02 '14 at 14:27

answered Jul 02 '14 at 14:14

Patrick Hofman

153,850
22
249
325

Where do i insert this in the query? – Mathlight Jul 02 '14 at 14:18
@TMNT2014: you are right. It was the other way around. – Patrick Hofman Jul 02 '14 at 14:19
@Mathlight: In the `where` clause. – Patrick Hofman Jul 02 '14 at 14:19
Maybe it's me, but when i copy paste your code into my query, it's not working... – Mathlight Jul 02 '14 at 14:22
I'm sorry. I meant that I still get an big list. But an `LEFT OUT JOIN` solved the problem. Thank you anyway for helping me! – Mathlight Jul 02 '14 at 14:24
That is true. If it doesn't match, it matches all. This works if the use case is right. Otherwise, you should indeed use `left outer join`. – Patrick Hofman Jul 02 '14 at 14:25

score 0 · Answer 3 · answered Jul 02 '14 at 14:18

0

Instead of inner join you need to use left join to get the missing rows.

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

answered Jul 02 '14 at 14:18

Bhasyakarulu Kottakota

823
1
6
10

I still get an big list with the Taak (that has an null `idPartij` ) multiplied by all `Partijen` – Mathlight Jul 02 '14 at 14:20
In that case you can use LEFT OUTER JOIN (http://stackoverflow.com/questions/406294/left-join-and-left-outer-join-in-sql-server) – Bhasyakarulu Kottakota Jul 02 '14 at 14:34

score 0 · Answer 4 · answered Jul 02 '14 at 14:23

0

If you want to join the tables so that (a) non-null equivalent Ids match, and (b) null Ids match, then you're query should look like:

SELECT T.idTaak,
       T.Taaktype,
       P.Partijnaam,
       T.Naar,
       T.UltimatumDatum,
       T.Opmerking,
       T.Status,
       T.Prioriteit
FROM Taak AS T INNER JOIN Partij AS P
   ON T.idPartij = P.idPartij OR T.idPartij IS NULL AND P.idPartij IS NULL
ORDER BY idTaak DESC

answered Jul 02 '14 at 14:23

Michael Petito

12,891
4
40
54

an `LEFT OUTER JOIN` worked for me. If I understand it right [from here](http://stackoverflow.com/questions/38549/difference-between-inner-and-outer-joins) does an left outer join return an null instead of nothing. And i want an null... – Mathlight Jul 02 '14 at 14:26
1

Use `LEFT OUTER JOIN` to return rows that match on the left hand side regardless if any rows match on the right side. I wasn't sure from your question if you were trying to match rows that exist on both sides where both have null `idPartij`. – Michael Petito Jul 02 '14 at 14:29
Alright, thank you for the clarification. Now I'm sure I need the `LEFT OUTER`. – Mathlight Jul 02 '14 at 14:30

SQL Where ID equals ID but can be null

4 Answers4

Linked