SQL Server: Secondary query on empty rowset from first query

Question

say I have the following simple table

table a { id int, pointId int, state int, value int }

id    pointId     state   value
-------------------------------
1     1           2       xxxxx
2     3           2       xxxxx
3     3           2       xxxxx
4     3           1       xxxxx

with the following two queries:

SELECT * FROM a WHERE state = 1 and pointId = x
SELECT * FROM a WHERE state = 2 and pointId = x

Is there a way to combine the two queries so that:

if query 1 returns >0 rows, it returns the result from query 1
if query 1 returns 0 rows, it returns the result from query 2

(so for pointId = 1 it would return row 1, for pointId = 3 it would return row 4)

I've been trying to combine the two with a union doing something like

SELECT * FROM a WHERE state = 1 AND pointId = x
UNION ALL
SELECT * FROM a WHERE state = 2 AND pointId = x and 
pointId NOT IN (SELECT pointId FROM a WHERE state = 1 AND pointId = x)

I'm wondering if I'm making this way to complicated and if there's a more easy way. I realize I could just run the first query and then handle it in my code, but it would be really nice if I could do this in the sql. Any advice or pointers would be great!

Thanks,

A

You shouldn't use UNION in this case. You are not looking to combine records - you are looking to process either query 1 or query 2 based on EXISTS. — JonH
– JonH, Commented Jan 30, 2012 at 19:48

Community · Accepted Answer · 2017-05-23 11:43:45Z

3

Here is the best answer in terms of maintenance and performance. Source:https://stackoverflow.com/a/12981314

WITH query1 AS (
    SELECT ...
),
query2 AS (
    SELECT ...
)
SELECT *
    FROM query1
UNION ALL
SELECT *
    FROM query2
    WHERE NOT EXISTS (
        SELECT NULL
        FROM query1
    );

edited May 23, 2017 at 11:43

CommunityBot

11 silver badge

answered May 27, 2015 at 14:04

Valerio Gentile

1,11812 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

JonH · Accepted Answer · 2012-01-30 19:46:21Z

0

UNION is used to combine tuples, your specification mentions you want to return 1 set of tuples or another set of tuples. In this case you don't want to use UNION, you can use EXISTS:

IF EXISTS(...insert your first query here...)
    BEGIN
       --a record exists process first query
    END
ELSE
    BEGIN
         --do other query
    END

answered Jan 30, 2012 at 19:46

JonH

33.2k13 gold badges93 silver badges149 bronze badges

2 Comments

user1178830 Over a year ago

Right:) However, that would execute the query twice no? The table a from my example is a huge table in reality and I was wondering if there's a way to avoid doing the query twice... (but thanks a lot already!)

JonH Over a year ago

You aren't processing or returning the EXISTS clause. You are checking if a record exists. What you could also do is check if the count > 0 SELECT COUNT(*) FROM Query1 and if that returns > 0 execute query 1 else execute query 2.

Martin Smith · Accepted Answer · 2012-01-30 20:00:58Z

0

For the specific case in your question you could do

WITH T
     AS (SELECT *,
                DENSE_RANK() OVER (ORDER BY state) AS R
         FROM   a
         WHERE  state IN ( 1, 2 )
                AND pointId = x)
SELECT id,
       pointId,
       state,
       value
FROM   T
WHERE  R = 1;

More generally if Query 1 is potentially expensive you could just execute it and then check @@ROWCOUNT afterwards to know whether to execute Query 2.

This means your application needs to handle multiple result sets. If you don't want to handle that the first query could be inserted to a table variable then you only do the SELECT from that if @@ROWCOUNT > 0

edited Jan 30, 2012 at 20:00

answered Jan 30, 2012 at 19:51

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

Collectives™ on Stack Overflow

SQL Server: Secondary query on empty rowset from first query

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related