Short-circuit UNION? (only execute 2nd clause if 1st clause has no results)

Question

If I do:

SELECT * FROM A   
WHERE conditions  
UNION  
SELECT * FROM B   
WHERE conditions 

I get the union of the resultset of query of A and resultset of query of B.

Is there a way/operator so that I can get a short-circuit OR result instead?

I.e. Get the result of SELECT * FROM A WHERE conditions and only if this returns nothing get the resultset of the SELECT * FROM B WHERE conditions ?

Answer 1

The short answer is no, but you can avoid the second query, but you must re-run the first:

SELECT * FROM A   
WHERE conditions  
UNION  
SELECT * FROM B   
WHERE NOT EXISTS (
    SELECT * FROM A   
    WHERE conditions)
AND conditions

This assumes the optimizer helps out and short circuits the second query because the result of the NOT EXISTS is false for all rows.

If the first query is much cheaper to run than the second, you would probably gain performance if the first row returned rows.

Answer 2

You can do this with a single SQL query as:

SELECT *
FROM A   
WHERE conditions  
UNION ALL
SELECT *
FROM B   
WHERE conditions and not exists (select * from A where conditions);

Answer 3

Have you tried an 'If' Statement?

If(select Count(*) from A where conditions) > 0 
Then Select * from A where conditions
Else Select * from B where conditions

Answer 4

I did not need to check against the return of "nothing" in the first union block, but against "x number of rows".

Just sharing this for those who might search for this here as well, and taking up the accepted answer here + the answer of this user.

You can check against the count of the rows of the "first block of the union"-subquery like this:

select col_b from (
(
    SELECT * FROM A   
    WHERE conditions
    ORDER BY 
    LIMIT (0,5)
) 
UNION
(
    SELECT * FROM B   
    WHERE 5 not in (
        select count(1) from (
            SELECT * FROM A   
            WHERE conditions
            limit 0,5) tbl_sub
        )
    AND conditions
    ORDER BY col_a
    LIMIT (0,5)
)
) tbl

I could use it like this, but I did not check this pseudocode thoroughly against my use case, no guarantees that this is 100% right.

In your case, you would not use it anyway, but you could, taking 0 instead of 5, and you would need a limit 0,1 to check against 0 rows. The limit 0,1 speeds up such rows-count checks against > 0 or in my case a rows-count check for = 1.