if select * from table where x=1
returns 0 rows, then I need select * from table where x=2 [or some other query]
. Is it possible to do this in a single MySQL query with a conditional statement?
Edit: All answers with UNION
work, but only if both queries select from the same table (or tables with the same number of columns). What if the second query is applied on a different table with joins?
Let me write down the my queries to make the question more clear:
SELECT table1.a, table2.b from table1 LEFT JOIN table2 ON table2.x= table1.x
WHERE .....
if the result from the 1st one is null then:
SELECT table1.a FROM table1
WHERE ....
I will be using the rows from the 1st query if it returns any, otherwise the 2nd one will be used.
The syntax for the IF-THEN-ELSE statement in MySQL is: IF condition1 THEN {... statements to execute when condition1 is TRUE...} [ ELSEIF condition2 THEN {...
The common table expression ( WITH clause) wraps the first query that we want to execute no matter what. We then select from the first query, and use UNION ALL to combine the result with the result of the second query, which we're executing only if the first query didn't yield any results (through NOT EXISTS ).
MySQL IF-THEN-ELSEIF-ELSE statement In this syntax, if the condition evaluates to TRUE , the statements in the IF-THEN branch executes; otherwise, the next elseif-condition is evaluated. If the elseif-condition evaluates to TRUE , the elseif-statement executes; otherwise, the next elseif-condition is evaluated.
This appears to work from a quick test I just did and avoids the need to check for the existence of x=1
twice.
SELECT SQL_CALC_FOUND_ROWS *
FROM mytable
WHERE x = 1
UNION ALL
SELECT *
FROM mytable
WHERE
FOUND_ROWS() = 0 AND x = 2;
Edit: Following your clarification to the question obviously the 2 queries will need to be UNION compatible for the above to work.
The answer to your updated question is No. This is not possible in a single query. You would need to use some conditional procedural logic to execute the desired query.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With