MYSQL if a select query returns 0 rows then another select query?
Solution 1
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.
Solution 2
You could try...
SELECT *
FROM mytable
WHERE x = 1
UNION
SELECT *
FROM mytable
WHERE x = 2 AND
NOT EXISTS (SELECT *
FROM mytable
WHERE x = 1);
if you don't consider it too ghastly a hack.
Related videos on Youtube
emre
Updated on April 27, 2022Comments
-
emre about 2 years
if
select * from table where x=1
returns 0 rows, then I needselect * 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:
1st:
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:
2nd:
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.
-
Brian Hooper almost 14 yearsThank you for that, Mr Smith. I didn't know about SQL_CALC_FOUND_ROWS.
-
emre almost 14 yearswill this query return the rows from (SELECT * FROM t2) it any results EXIST(S)? because I will be needing them in that case...
-
lurscher over 6 yearsthe problem seems to be that
FOUND_ROWS()
will return a value for the next executed query, theUNION ALL
considers both queries to be the same -
HASSAN MD TAREQ about 5 yearsAnd how is this an answer without showing SQL query?