How to efficiently check EXISTS on multiple columns?

Question

This is an issue I come up against periodically and have not yet found a good solution for.

Supposing the following table structure

CREATE TABLE T
(
A INT PRIMARY KEY,
B CHAR(1000) NULL,
C CHAR(1000) NULL
)

and the requirement is to determine whether either of the nullable columns B or C actually contain any NULL values (and if so which one(s)).

Also assume the table contains millions of rows (and that no column statistics are available that could be peeked at as I am interested in a more generic solution for this class of queries).

I can think of a few ways of approaching this but all have weaknesses.

Two separate EXISTS statements. This would have the advantage of allowing the queries to stop scanning early as soon as a NULL is found. But if both columns in fact contain no NULLs then two full scans will result.

Single Aggregate Query

SELECT 
    MAX(CASE WHEN B IS NULL THEN 1 ELSE 0 END) AS B,
    MAX(CASE WHEN C IS NULL THEN 1 ELSE 0 END) AS C
FROM T

This could process both columns at the same time so have a worst case of one full scan. The disadvantage is that even if it encounters a NULL in both columns very early on the query will still end up scanning the whole of the rest of the table.

User variables

I can think of a third way of doing this

BEGIN TRY
DECLARE @B INT, @C INT, @D INT

SELECT 
    @B = CASE WHEN B IS NULL THEN 1 ELSE @B END,
    @C = CASE WHEN C IS NULL THEN 1 ELSE @C END,
    /*Divide by zero error if both @B and @C are 1.
    Might happen next row as no guarantee of order of
    assignments*/
    @D = 1 / (2 - (@B + @C))
FROM T  
OPTION (MAXDOP 1)       
END TRY
BEGIN CATCH
IF ERROR_NUMBER() = 8134 /*Divide by zero*/
    BEGIN
    SELECT 'B,C both contain NULLs'
    RETURN;
    END
ELSE
    RETURN;
END CATCH

SELECT ISNULL(@B,0),
       ISNULL(@C,0)

but this is not suitable for production code as the correct behavior for an aggregate concatenation query is undefined. and terminating the scan by throwing an error is quite a horrible solution anyway.

Is there another option that combines the strengths of the approaches above?

Edit

Just to update this with the results I get in terms of reads for the answers submitted so far (using @ypercube's test data)

+----------+------------+------+---------+----------+----------------------+----------+------------------+
|          | 2 * EXISTS | CASE | Kejser  |  Kejser  |        Kejser        | ypercube |       8kb        |
+----------+------------+------+---------+----------+----------------------+----------+------------------+
|          |            |      |         | MAXDOP 1 | HASH GROUP, MAXDOP 1 |          |                  |
| No Nulls |      15208 | 7604 |    8343 | 7604     | 7604                 |    15208 | 8346 (8343+3)    |
| One Null |       7613 | 7604 |    8343 | 7604     | 7604                 |     7620 | 7630 (25+7602+3) |
| Two Null |         23 | 7604 |    8343 | 7604     | 7604                 |       30 | 30 (18+12)       |
+----------+------------+------+---------+----------+----------------------+----------+------------------+

For @Thomas's answer I changed TOP 3 to TOP 2 to potentially allow it to exit earlier. I got a parallel plan by default for that answer so also tried it with a MAXDOP 1 hint in order to make the number of reads more comparable to the other plans. I was somewhat surprised by the results as in my earlier test I had seen that query short circuit without reading the whole table.

The plan for my test data that short circuits is below

The plan for ypercube's data is

So it adds a blocking sort operator to the plan. I also tried with the HASH GROUP hint but that still ends up reading all the rows

So the key seems to be to get a hash match (flow distinct) operator to allow this plan to short circuit as the other alternatives will block and consume all rows anyway. I don't think there is hint to force this specifically but apparently "in general, the optimiser chooses a Flow Distinct where it determines that fewer output rows are required than there are distinct values in the input set.".

@ypercube's data only has 1 row in each column with NULL values (table cardinality = 30300) and the estimated rows going into and out of the operator are both 1. By making the predicate a bit more opaque to the optimiser it generated a plan with the Flow Distinct operator.

SELECT TOP 2 *
FROM (SELECT DISTINCT 
        CASE WHEN b IS NULL THEN NULL ELSE 'foo' END AS b
      , CASE WHEN c IS NULL THEN NULL ELSE 'bar' END AS c
  FROM test T 
  WHERE LEFT(b,1) + LEFT(c,1) IS NULL
) AS DT 

Edit 2

One last tweak that occurred to me is that the query above could still end up processing more rows than necessary in the event that the first row it encounters with a NULL has NULLs in both column B and C. It will continue scanning rather than exiting immediately. One way of avoiding this would be to unpivot the rows as they are scanned. So my final amend to Thomas Kejser's answer is below

SELECT DISTINCT TOP 2 NullExists
FROM test T 
CROSS APPLY (VALUES(CASE WHEN b IS NULL THEN 'b' END),
                   (CASE WHEN c IS NULL THEN 'c' END)) V(NullExists)
WHERE NullExists IS NOT NULL

It would probably be better for the predicate to be WHERE (b IS NULL OR c IS NULL) AND NullExists IS NOT NULL but against the previous test data that one doesn't give me a plan with a Flow Distinct, whereas the NullExists IS NOT NULL one does (plan below).

Answer 1

How about:

SELECT TOP 3 *
FROM (SELECT DISTINCT 
        CASE WHEN B IS NULL THEN NULL ELSE 'foo' END AS B
        , CASE WHEN C IS NULL THEN NULL ELSE 'bar' END AS C
  FROM T 
  WHERE 
    (B IS NULL AND C IS NOT NULL) 
    OR (B IS NOT NULL AND C IS NULL) 
    OR (B IS NULL AND C IS NULL)
) AS DT

Answer 2

As I understand the question, you want to know whether a null exists in any of the columns values as opposed to actually returning the rows in which either B or C is null. If that is the case, then why not:

Select Top 1 'B as nulls' As Col
From T
Where T.B Is Null
Union All
Select Top 1 'C as nulls'
From T
Where T.C Is Null

On my test rig with SQL 2008 R2 and one million rows, I got the following results in ms from the Client Statistics tab:

Kejser                          2907,2875,2829,3576,3103
ypercube                        2454,1738,1743,1765,2305
OP single aggregate solution    (stopped after 120,000 ms) Wouldn't even finish
My solution                     1619,1564,1665,1675,1674

If you add the nolock hint, the results are even faster:

Select Top 1 'B as nulls' As Col
From T With(Nolock)
Where T.B Is Null
Union All
Select Top 1 'C as nulls'
From T With(Nolock)
Where T.C Is Null

My solution (with nolock)       42,70,94,138,120

For reference I used Red-gate's SQL Generator to generate the data. Out of my one million rows, 9,886 rows had a null B value and 10,019 had a null C value.

In this series of tests, every row in column B has a value:

Kejser                          245200  Scan count 1, logical reads 367259, physical reads 858, read-ahead reads 367278
                                250540  Scan count 1, logical reads 367259, physical reads 860, read-ahead reads 367280

ypercube(1)                     249137  Scan count 2, logical reads 367276, physical reads 850, read-ahead reads 367278
                                248276  Scan count 2, logical reads 367276, physical reads 869, read-ahead reads 368765

My solution                     250348  Scan count 2, logical reads 367276, physical reads 858, read-ahead reads 367278
                                250327  Scan count 2, logical reads 367276, physical reads 854, read-ahead reads 367278

Before each test (both sets) I ran CHECKPOINT and DBCC DROPCLEANBUFFERS.

Here are the results when there are no nulls in the table. Note that the 2 exists solution provided by ypercube are nearly identical to mine in terms of reads and execution time. I (we) believe this is due to advantages of Enterprise/Developer edition having use of Advanced Scanning. If you were using only the Standard edition or lower, Kejser's solution may very well be the fastest solution.

Kejser                          248875  Scan count 1, logical reads 367259, physical reads 860, read-ahead reads 367290

ypercube(1)                     243349  Scan count 2, logical reads 367265, physical reads 851, read-ahead reads 367278
                                242729  Scan count 2, logical reads 367265, physical reads 858, read-ahead reads 367276
                                242531  Scan count 2, logical reads 367265, physical reads 855, read-ahead reads 367278

My solution                     243094  Scan count 2, logical reads 367265, physical reads 857, read-ahead reads 367278
                                243444  Scan count 2, logical reads 367265, physical reads 857, read-ahead reads 367278

Answer 3

Are IF statements allowed?

This should allow you to confirm existence of B or C on one pass through the table:

DECLARE 
  @A INT, 
  @B CHAR(10), 
  @C CHAR(10)

SET @B = 'X'
SET @C = 'X'

SELECT TOP 1 
  @A = A, 
  @B = B, 
  @C = C
FROM T 
WHERE B IS NULL OR C IS NULL 

IF @@ROWCOUNT = 0 
BEGIN 
  SELECT 'No nulls'
  RETURN
END

IF @B IS NULL AND @C IS NULL
BEGIN
  SELECT 'Both null'
  RETURN
END 

IF @B IS NULL 
BEGIN
  SELECT TOP 1 
    @C = C
  FROM T
  WHERE A > @A
  AND C IS NULL

  IF @B IS NULL AND @C IS NULL 
  BEGIN
    SELECT 'Both null'
    RETURN
  END
  ELSE
  BEGIN
    SELECT 'B is null'
    RETURN
  END
END

IF @C IS NULL 
BEGIN
  SELECT TOP 1 
    @B = B
  FROM T 
  WHERE A > @A
  AND B IS NULL

  IF @C IS NULL AND @B IS NULL
  BEGIN
    SELECT 'Both null'
    RETURN
  END
  ELSE
  BEGIN
    SELECT 'C is null'
    RETURN
  END
END      

Answer 4

Tested in SQL-Fiddle in versions: 2008 r2 and 2012 with 30K rows.

• The EXISTS query shows a huge benefit in efficiency when it finds Nulls early - which is expected.
• I get better performance with the EXISTS query - in all cases in 2012, which I can't explain.
• In 2008R2, when there are no Nulls, it's slower than the other 2 queries. The more early it finds the Nulls, the faster it gets and when both columns have nulls early, it's much faster than the other 2 queries.
• Thomas Kejser's query seems to perform slightly but constantly better in 2012 and worse in 2008R2, compared to Martin's CASE query.
• 2012 version seems to have far better performance. It may have to do with the SQL-Fiddle servers' settings though and not only with improvements on the optimizer.

Queries and timings. Timings where done:

• 1st with no Nulls at all
• 2nd with column B having one NULL at a small id.
• 3nd with both columns having one NULL each at small ids.

Here we go (there is an issue with the plans, I'll try again later. Follow the links for now):

---

Query with 2 EXISTS subqueries

SELECT 
      CASE WHEN EXISTS (SELECT * FROM test WHERE b IS NULL)
             THEN 1 ELSE 0 
      END AS B,
      CASE WHEN EXISTS (SELECT * FROM test WHERE c IS NULL)
             THEN 1 ELSE 0 
      END AS C ;

-------------------------------------
Times in ms (2008R2): 1344 - 596 -  1  
Times in ms   (2012):   26 -  14 -  2

---

Martin Smith's Single Aggregate Query

SELECT 
    MAX(CASE WHEN b IS NULL THEN 1 ELSE 0 END) AS B,
    MAX(CASE WHEN c IS NULL THEN 1 ELSE 0 END) AS C
FROM test ;

--------------------------------------
Times in ms (2008R2):  558 - 553 - 516  
Times in ms   (2012):   37 -  35 -  36

---

Thomas Kejser's query

SELECT TOP 3 *
FROM (SELECT DISTINCT 
        CASE WHEN B IS NULL THEN NULL ELSE 'foo' END AS b
      , CASE WHEN C IS NULL THEN NULL ELSE 'bar' END AS c
  FROM test T 
  WHERE 
    (B IS NULL AND C IS NOT NULL) 
    OR (B IS NOT NULL AND C IS NULL) 
    OR (B IS NULL AND C IS NULL)
) AS DT ;

--------------------------------------
Times in ms (2008R2):  859 - 705 - 668  
Times in ms   (2012):   24 -  19 -  18

---

My suggestion (1)

WITH tmp1 AS
  ( SELECT TOP (1) 
        id, b, c
    FROM test
    WHERE b IS NULL OR c IS NULL
    ORDER BY id 
  ) 

  SELECT 
      tmp1.*, 
      NULL AS id2, NULL AS b2, NULL AS c2
  FROM tmp1
UNION ALL
  SELECT *
  FROM
    ( SELECT TOP (1)
          tmp1.id, tmp1.b, tmp1.c,
          test.id AS id2, test.b AS b2, test.c AS c2 
      FROM test
        CROSS JOIN tmp1
      WHERE test.id >= tmp1.id
        AND ( test.b IS NULL AND tmp1.c IS NULL
           OR tmp1.b IS NULL AND test.c IS NULL
            )
      ORDER BY test.id
    ) AS x ;

--------------------------------------
Times in ms (2008R2): 1089 - 572 -  16   
Times in ms   (2012):   28 -  15 -   1

It needs some polishing on the output but the efficiency is similar to the EXISTS query. I thought it would be better when there are no nulls but testing shows it's not.

---

Suggestion (2)

Trying to simplify the logic:

CREATE TABLE tmp
( id INT
, b CHAR(1000)
, c CHAR(1000)
) ;

DELETE  FROM tmp ;

INSERT INTO tmp 
    SELECT TOP (1) 
        id, b, c
    FROM test
    WHERE b IS NULL OR c IS NULL
    ORDER BY id  ; 

INSERT INTO tmp 
    SELECT TOP (1)
        test.id, test.b, test.c 
      FROM test
        JOIN tmp 
          ON test.id >= tmp.id
      WHERE ( test.b IS NULL AND tmp.c IS NULL
           OR tmp.b IS NULL AND test.c IS NULL
            )
      ORDER BY test.id ;

SELECT *
FROM tmp ;

It seems to perform better in 2008R2 than the previous suggestion but worse in 2012 (perhaps the 2nd INSERT can be rewritten using IF, like @8kb's answer):

------------------------------------------
Times in ms (2008R2): 416+6 - 1+127 -  1+1   
Times in ms   (2012):  14+1 - 0+27  -  0+29

Answer 5

When you use EXISTS, SQL Server knows you are doing an existence check. When it finds the first matching value, it returns TRUE and stops looking.

when you concatinate 2 columns and if any is null the result will be null

e.g

null + 'a' = null

so check this code

IF EXISTS (SELECT 1 FROM T WHERE B+C is null)
SELECT Top 1 ISNULL(B,'B ') + ISNULL(C,'C') as [Nullcolumn] FROM T WHERE B+C is null

Answer 6

How about:

select 
    exists(T.B is null) as 'B is null',
    exists(T.C is null) as 'C is null'
from T;

If this works (I haven't tested it), it would yield a one-row table with 2 columns, each one either TRUE or FALSE. I didn't test the efficiency.