How to use null value IN operator SQL

Question

I have a problem with column [Transfer-from Code].
There is like 30 different values.
But I need all values for [Transfer-from Code] which are x1, x2 and NULL.

Here is the code:

SELECT 
       ILE.[Item No_]           AS ItemNo
      ,It.[Tariff No_]          AS TariffNo
      ,ILE.[Posting Date]       AS PostingDate
      ,ILE.[Location Code]      AS LocationCode
      ,ILE.[Quantity]           AS Qty
      ,CASE 
       WHEN ILE.[Entry Type] = 0 THEN 'Purchase'    
       WHEN ILE.[Entry Type] = 4 THEN 'Transfer'
       ELSE '' END AS EntryType
      ,Trp.[Transfer-from Code] AS SourceLocation
      -- Try to cast from below all NULL values to be test, so I can use them into IN operator. Still not working
      --,ISNULL(Trp.[Transfer-from Code], 'test') AS SourceLocation

FROM [MDR].[dbo].[nav_XX$Item_Ledger_Entry]             AS ILE 
LEFT JOIN [MDR].[dbo].[nav_XX$Item]                     AS It ON ILE.[Item No_] = It.No_
LEFT JOIN [MDR].[dbo].[nav_XX$Transfer_Receipt_Header]  AS Trp on ILE.[Document No_] = Trp.No_

WHERE ILE.[Entry Type] IN ('0','4')
AND ILE.[Posting Date] > '2019-12-11 00:00:00.000'
AND ILE.[Location Code] IN ('X24','XB16')

--From this part below, everything don't work. I'm getting only the values for X1 and X2.

--AND TRP.[Transfer-from Code] IN ('X1','X2',NULL)

--AND TRP.[Transfer-from Code] IN ('X1','X2','test')

/*
AND TRP.[Transfer-from Code] = 'X1'
OR TRP.[Transfer-from Code] = 'X2'
--OR TRP.[Transfer-from Code]= NULL
OR TRP.[Transfer-from Code]= 'test'
*/

Can someone tell me where is my mistake?

@DmitryBychenko AND TRP.[Transfer-from Code] IN ('X1','X2', IS NULL) it not working. I receive error:Incorrect syntax near the keyword 'IS'. — mns
– mns, Commented Dec 13, 2019 at 13:18
where TRP.[Transfer-from Code] IN ('X1','X2') is not false will return x1, x2 and null. — jarlh
– jarlh, Commented Dec 13, 2019 at 13:31

forpas · Accepted Answer · 2019-12-13 13:15:30Z

2

Your condition should be:

AND (TRP.[Transfer-from Code] IN ('X1','X2') OR TRP.[Transfer-from Code] IS NULL)

Check for NULLs only with the operators IS and IS NOT.
Never with = or IN because the result of a comparison to NULL is always NULL.

answered Dec 13, 2019 at 13:15

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Killer Queen · Accepted Answer · 2019-12-13 13:20:18Z

1

Replace "=" with "IS", add "()" and it will work.

Try :

AND 
(
    TRP.[Transfer-from Code] = 'X1'
    OR TRP.[Transfer-from Code] = 'X2'
    OR TRP.[Transfer-from Code] IS NULL
)

answered Dec 13, 2019 at 13:20

Killer Queen

7569 silver badges22 bronze badges

Comments

Dmitrii Bychenko · Accepted Answer · 2019-12-13 13:21:06Z

1

Null has a special meaning it stands for unknown, doesn't matter etc. That's why it has special syntax to check:

 field IS NULL

In your case

 (field IN (item1, item2, item3) OR field IS NULL)

E.g.

 (TRP.[Transfer-from Code] IN ('X1', 'X2') OR TRP.[Transfer-from Code] IS NULL)

please, note that even if text like

 field = NULL

or

 field IN (null)

is syntactically correct, the outcome will be neither true nor false but NULL when field is NULL (Yes, NULL = NULL is not true):

if field with unknown value (Null) equals to some other unknown value (Null)? It's unknown (Null)

edited Dec 13, 2019 at 13:21

answered Dec 13, 2019 at 13:15

Dmitrii Bychenko

188k20 gold badges178 silver badges231 bronze badges

1 Comment

jarlh Over a year ago

Note that field = NULL and field IN (null) are not valid ANSI SQL syntax. (But accepted by many dbms products.)

Collectives™ on Stack Overflow

How to use null value IN operator SQL

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related