Search code examples
sqlsql-servert-sqlnull

Compare NULL to NULL in SQL Server

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
Solution 
  •   SELECT * 
  FROM Customers
  WHERE EXISTS (SELECT OrderID INTERSECT SELECT @OrderID)

    Will do this efficiently.

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