How to "short circuit"/conditionally add run union clause in a mysql query?

Question

I am selecting some rows from different tables The logic is like this: I want to return in total only 10 results, I try first table_a and if I cannot fetch 10 rows from table_a I fill the remaining rows from the results fetched from table_b

Pseudo-query

   select value from table_a limit 10
   union # ONLY IF COULD NOT FETCH 10 values from table_a
   (select value from table_b limit 10)
   limit 10

Is something like this possible in mysql and if yes how ?

Some simple test cases

test case 1: that if I can fetch 10 rows form table_a don't want to execute the union as I have enough results.

test case 2: I got 4 values(rows) from table_a then union executes and I get N more rows from table_b, in total should never get more than 10 rows (limit 10)

test case 3: 0 items in table_a, the query will only return up to at most 10 items from table_b

PS: I am aware and know how to solve this via the programming language.

In that case would be as easy as:

$queryA = 'select value from table_a limit 10';
$results = $sql->execute($queryA);
if (count($results) < 10) {
  $queryB = sprint('select value from table_b limit %d', 10 - count($results));
  $resultsB = $sql->execute($queryB);
  $results = $results + $resultsB;
}

I wonder if can be done in a more elegant minimal way directly from mysql

If you don't want to run the second query unless the first returns fewer than 10 rows, then do it in code as you show. — Bill Karwin
– Bill Karwin, Commented Oct 10, 2022 at 15:50

Ankit Bajpai · Accepted Answer · 2022-10-10 13:03:46Z

0

You can have a dummy column to count the rows from table_a and then can use a sub-query to have your desired result -

SELECT value
  FROM (SELECT 1 as src, value
          FROM table_a
         UNION
        SELECT 2 as src, value
          FROM table_b
        ORDER BY src
        LIMIT 10
       ) x;

answered Oct 10, 2022 at 13:03

Ankit Bajpai

13.6k4 gold badges27 silver badges43 bronze badges

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

4 Comments

Kristi Jorgji Over a year ago

Thanks for the answer but that will not "short circuit" or stop the select 2... the part after union from being executed

Ankit Bajpai Over a year ago

Even if that step executed, You will only get the 10 records from table_a only. If it does not have 10 records, Then only it will include records from table_b.

Kristi Jorgji Over a year ago

I know and understand that but will still execute the query and wait until those 10 rows are calculated and fetched. I am curious to know if there is a way to not execute the second query if have already 10 rows from first query therefore not wait X ms/s for the second query

Ankit Bajpai Over a year ago

Since I am using LIMIT 10. So it will never executes 2nd query if you get 10 rows from table_a only.

Collectives™ on Stack Overflow

How to "short circuit"/conditionally add run union clause in a mysql query?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related