如何编写可处理零返回记录的SQL CASE语句？

First, let’s clarify why your current CASE statement approach might not work as expected: when you use a subquery that returns zero rows directly in a CASE condition (like comparing it to NULL or checking >0), SQL can’t evaluate that as a scalar boolean value—this will either throw an error or return unexpected NULL results. Instead, we need to use set-checking functions like EXISTS or aggregate functions like COUNT() to convert the set result into a usable scalar for the CASE logic.

Solution 1: Use EXISTS (Most Efficient) #

EXISTS is ideal here because it stops searching as soon as it finds a matching row, making it faster than counting all rows for large tables. It returns TRUE if the subquery returns any rows, FALSE otherwise.

SELECT 
  CASE 
    -- Check if there are no rows in Latest_LI that aren't in PRIOR_LI (EXCEPT returns zero rows)
    WHEN NOT EXISTS (SELECT * FROM Latest_LI EXCEPT SELECT * FROM PRIOR_LI) THEN 1
    -- Check if there's at least one common row between the two tables
    WHEN EXISTS (SELECT * FROM Latest_LI INTERSECT SELECT * FROM PRIOR_LI) THEN 2
    -- Optional: Add an ELSE clause for edge cases (e.g., no common rows at all)
    ELSE 3
  END AS Result;

Note: The DISTINCT keyword in your original subqueries is redundant—EXCEPT and INTERSECT already return distinct rows by default, so you can safely remove it.

Solution 2: Use COUNT() for Row Count Checks #

If you need to explicitly check the number of rows returned by the set operations, you can wrap the subqueries in a COUNT() aggregate:

SELECT 
  CASE 
    WHEN (SELECT COUNT(*) FROM (SELECT * FROM Latest_LI EXCEPT SELECT * FROM PRIOR_LI) AS Differences) = 0 THEN 1
    WHEN (SELECT COUNT(*) FROM (SELECT * FROM Latest_LI INTERSECT SELECT * FROM PRIOR_LI) AS CommonRows) > 0 THEN 2
    ELSE 3
  END AS Result;

This works because the inner subquery creates a temporary result set, and COUNT(*) returns the number of rows in that set. Keep in mind that this will scan all matching rows (unlike EXISTS), so it’s less efficient for large datasets.

Comparing to Your Variable + IF Approach #

Your usual method of using variables and IF statements is totally valid, especially if you prefer procedural logic or need to reuse the values elsewhere in your script. Here’s how that might look with set checks:

DECLARE @no_differences BIT = 0;
DECLARE @has_common_rows BIT = 0;

-- Set @no_differences to 1 if EXCEPT returns zero rows
IF NOT EXISTS (SELECT * FROM Latest_LI EXCEPT SELECT * FROM PRIOR_LI)
  SET @no_differences = 1;

-- Set @has_common_rows to 1 if INTERSECT returns any rows
IF EXISTS (SELECT * FROM Latest_LI INTERSECT SELECT * FROM PRIOR_LI)
  SET @has_common_rows = 1;

-- Use IF logic to return the result
IF @no_differences = 1
  SELECT 1 AS Result;
ELSE IF @has_common_rows = 1
  SELECT 2 AS Result;
ELSE
  SELECT 3 AS Result;

The trade-off here is readability for some vs. the declarative nature of the CASE statement. Both approaches are correct—choose the one that fits your code style and performance needs.

内容的提问来源于stack exchange，提问作者bcascone