MYSQL if a select query returns 0 rows then another select query?

Question

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 selects 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.

Answer 1

yes

Subqueries with EXISTS or NOT EXISTS

http://dev.mysql.com/doc/refman/5.1/en/exists-and-not-exists-subqueries.html

example :

SELECT column1 FROM t1 WHERE NOT EXISTS (SELECT * FROM t2);

Answer 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.

Answer 3

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.

Answer 4

If the two queries return different number of columns, you can pad one of the results with empty columns and also add an identifier column first.

SELECT SQL_CALC_FOUND_ROWS 1 query_type, mytable.*, 
'' col1, '' col2, '' col3, '' col4
FROM mytable
WHERE x = 1

UNION ALL

SELECT 2, mytable2.*
FROM mytable2
WHERE 
FOUND_ROWS() = 0 AND x = 2;

Where mytable2 has 4 more columns than mytable.