CODEWITHSUNDEEP
mysqlsqlsetunionresultset
Jim
Jim

Reputation: 19572

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

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 ?

Upvotes: 3

Views: 2228

Answers (4)

questionto42
questionto42

Reputation: 9552

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

I share this for those who might search for this here as well. I take up the accepted answer here + the other answer here 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

For me, it did work, I could strongly speed up a query, I think it was from 40 min to 1 min. Yet, for the sake of anonymizing and standardizing, I wrote this dense dummy code that I could not test but that is at least ready to be shared, with the caveat that I cannot say for sure that it works.

In your case, you would take 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.

Upvotes: -1

Bohemian
Bohemian

Reputation: 425053

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.

Upvotes: 6

Gordon Linoff
Gordon Linoff

Reputation: 1269923

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);

Upvotes: 1

orgtigger
orgtigger

Reputation: 4122

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

Upvotes: 0

Related Questions