Compare NULL to NULL in SQL Server

Question

I have some code where variable used in the WHERE clause could be nullable. As a result I have to query data using IF ELSE to check for NULL. Is there anyway of writing in in one query?

DECLARE @OrderID UNIQUEIDENTIFIER

IF @OrderID IS NULL 
BEGIN
    SELECT * 
    FROM Customers
    WHERE OrderID IS NULL
END
ELSE
BEGIN
    SELECT * 
    FROM Customers
    WHERE OrderID = @OrderID
END

Orlosky

fixed copy paste error – Orlosky

Answer 1

  SELECT * 
  FROM Customers
  WHERE EXISTS (SELECT OrderID INTERSECT SELECT @OrderID)

Will do this efficiently.

See Undocumented Query Plans: Equality Comparisons for more about this.

Celka

@PaulSnow I've no idea what you mean by that. But, yes, you can use it in a join condition if that's what you are asking. CREATE TABLE #T1(X INT NULL UNIQUE);CREATE TABLE #T2(X INT NULL UNIQUE);SELECT * FROM #T1 JOIN #T2 ON EXISTS(SELECT #T1.X INTERSECT SELECT #T2.X);DROP TABLE #T1, #T2 – Celka

@MartinSmith, any difference to WHERE OrderID is NULL and @OrderID is NULL or OrderID = @OrderID ? – Communalize

@Communalize - Not as far as I'm aware. Both can give a plan with an index seek on OrderID – Celka

We have numerous code in SPs that check for and logs changes to Column values when updating a Row. Based on this Answer, I derived the following for equality comparisons of Nullable values while treating a Null as a comparable value. ` declare @a int = null declare @b int = 1 --Equality comparisons treating Nulls as comparable values: --If (@a = @b), ... if exists (select @a intersect select @b) select 'true' as [=] else select 'false' as [=] --If (@a != @b), ... if not exists (select @a intersect select @b) select 'true' as [!=] else select 'false' as [!=] ` – Sniper

Answer 2

Assuming you want all records where order_id is either null or equal to a provided order ID, using COALESCE will keep the original order ID if not null but yield the provided order ID if it was null. That is then compared with the provided order ID.

SELECT *
FROM customers c
WHERE COALESCE(c.order_id, @OrderID) = @OrderID;

---

Based on the correct version of the query, I'd either write it all out in the WHERE clause, or possibly COALESCE both sides to a value that is known not to appear in that column ever.

SELECT c.*
FROM customers c
WHERE (@OrderID IS NULL AND c.OrderId IS NULL) OR c.OrderId = @OrderId

-- OR

SELECT c.*
FROM customers c
WHERE COALESCE(@OrderId, '@@@') = COALESCE(c.OrderId, '@@@')

Hexagon

does not give desired results – Orlosky

Interpreted your question differently, based on the version that still had the copy + paste error. – Hexagon

none of these solution work for my case when the value of @OrderID is null – Orlosky

Answer 3

SQL Server 2022 and Azure now supports the IS [NOT] DISTINCT FROM syntax. This will do a proper null comparison.

SELECT * 
FROM Customers
WHERE OrderID IS NOT DISTINCT FROM @OrderID;

Covenant

Answer 4

try this

DECLARE @OrderID UNIQUEIDENTIFIER

SELECT * FROM Customers
WHERE ISNULL(@OrderID, 0) = 0 or @OrderID = @OrderID

Royal