How to efficiently check EXISTS on multiple columns?

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 `NULL`s 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).

``````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
``````
• I like this approach. There are a few possible issues that I address in edits to my question though. As written `TOP 3` could just be `TOP 2` as currently it will scan until it finds one of each of the following `(NOT_NULL,NULL)`,`(NULL,NOT_NULL)`,`(NULL,NULL)`. Any 2 out of those 3 would be sufficient - and if it finds `(NULL,NULL)` first then the second wouldn't be needed either. Also in order to short circuit the plan would need to implement the distinct via a `hash match (flow distinct)` operator rather than `hash match (aggregate)` or `distinct sort` Jun 17, 2012 at 12:33

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

``````

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

``````

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
``````

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
``````

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
``````

``````select