Where clause IN and value is NULL

Transact-SQL
1

There are some extra records that need to be included where an ID is null.
The where clause has all the id's but ignoring the null values.

Here is a couple attempts:

 --@Stages               dbo.int_tbltype READONLY

SELECT ISNULL(s.StageID, 0) AS StageID, o.OrderID
FROM Order o
LEFT JOIN Stage s ON s.StageID = o.StageID
AND ISNULL(s.StageID, 0) = o.StageID    
where s.StageID IN (0, 1, 11, 3, 13, 2, 4, 5, 6, 12, 7, 14)
--s.StageID IN (SELECT ID FROM @Stages)

Please note that the where clause will actually contain a table type.
I tried to return the null id's from the select and from the join.
Both failed.

Suggestions?

Thanks!!!

2

Try moving the filter in the WHERE clause to the JOIN condition. That will give you all the rows in the Order table regardless of whether they have a corresponding row in the Stage table or not.

SELECT  ISNULL(s.StageID, 0) AS StageID ,
        o.OrderID
FROM    Order o
        LEFT JOIN Stage s ON 
		s.StageID = o.StageID
		AND s.StageID IN (0, 1, 11, 3, 13, 2, 4, 5, 6, 12, 7, 14)
3

Thanks for the suggestion but that didn't work.
Even with the ISNULL(s.StageID, 0) AS StageID in the select, it does come back 0 if it is null but the IN contained in the Where clause will not select it. Tested it without the IN statement.

4

Are you still using a WHERE clause? It sounds like it. When you do a LEFT JOIN to a table (in your case table Stage) and then use columns from that table in the WHERE clause the way you are doing, you are effectively turning the join into an INNER JOIN. That is why I removed the WHERE clause and changed the JOIN condition.

Can you post DDL for your tables and some sample data?

5

Not sure exactly what you are trying to do, but it's best to explicitly specify NULL conditions for clarity. Also, never use ISNULL() in a WHERE or JOIN, for performance reasons.

SELECT ISNULL(s.StageID, 0) AS StageID, o.OrderID
FROM Order o
LEFT JOIN Stage s ON s.StageID = o.StageID
     OR (s.StageID IS NULL AND o.StageID = 0)
where s.StageID IS NULL OR s.StageID IN (0, 1, 11, 3, 13, 2, 4, 5, 6, 12, 7, 14)