SQL Server: Compare nullable column in where clause

Question

I got a beginner's question about SQL server. I'm linking two tables with each other by setting a column of table A equal to a column in table B. Basically, my query looks like that:

SELECT A.nr, [...]
FROM A, B
WHERE A.id = B.id
AND [...]

My problem is now that the id column in the table B also has some null values. So, if B.id is null, the whole row won't be shown because the condition isn't met. But if B.id is null, I just want A.nr also to be null instead of the whole row just not showing up. How can I do that?

I thought about the following:

SELECT A.nr, [...]
FROM A, B
WHERE A.id = B.id OR B.id IS NULL
AND [...]

but then the column is just mixed with all the possible B.id rows, no?

Answer 1

The correct way to write this logic is to use an explicit JOIN. Never use commas in the FROM clause. Always use proper, explicit, standard JOIN syntax.

In SQL Server, you need to express this as:

SELECT A.nr, [...]
FROM A JOIN
     B
     ON (A.id = B.id OR (a.id IS NULL AND b.id IS NULL) )
WHERE . . .;

ANSI standard SQL has a comparison operator for this purpose, but SQL Server does not support it. This is called a NULL-safe comparison:

SELECT A.nr, [...]
FROM A JOIN
     B
     ON A.id IS NOT DISTINCT FROM B.id 
WHERE . . .;