SQL WHERE is misplaced?

Question

I would like to select rows from database where the ArkivDate is null.

I tried this:

SELECT
    * 
FROM
    [Bums].[dbo].[Items] 
INNER JOIN 
    [Bums].[dbo].[ItemsArkivFormUsed] ON [Bums].[dbo].[ItemsArkivFormUsed].[ItemNumber] = [Bums].[dbo].[Items].[ItemNumber] 
WHERE 
    [Bums].[dbo].[ItemsArkivFormUsed].[ArkivDate] = null

The query works if I remove the WHERE clause, but when I include it, the result returns empty.

I'm wondering, have I misplaced the WHERE?

The tables does have some null ArkivDate but my query can't filter to only display them

Can I anyone help me please

And [Is there any difference between IS NULL and =NULL](http://stackoverflow.com/questions/3777230/is-there-any-difference-between-is-null-and-null) — Lukasz Szozda, Mar 30 '16 at 14:25
This kind is easily one of the most frequently asked questions. Folks at SO should pop up the answer before anyone gets to post this question :) — SouravA, Mar 30 '16 at 14:25

Jamiec · Accepted Answer · 2016-03-30T14:25:59.543

2

NULL is a strange beast in sql, you need to use the syntax IS NULL rather than = NULL when ANSI_NULLS is set to on, which is the default.

SELECT * FROM [Bums].[dbo].[Items] 
INNER JOIN [Bums].[dbo].[ItemsArkivFormUsed] 
    ON [Bums].[dbo].[ItemsArkivFormUsed].[ItemNumber] = [Bums].[dbo].[Items].[ItemNumber] 
WHERE [Bums].[dbo].[ItemsArkivFormUsed].[ArkivDate] IS NULL

See docs on SET ANS_NULL for more info

edited Mar 30 '16 at 14:25

answered Mar 30 '16 at 14:24

Jamiec

133,658
13
134
193

Thank you, it is working now – Simon.S Mar 30 '16 at 14:25

SQL WHERE is misplaced?

1 Answers1