Avoiding IF ELSE due to variable been NULL

问题:

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

回答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.

回答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, '@@@')

回答3:

try this

DECLARE @OrderID UNIQUEIDENTIFIER

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