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

Is there a reason you want to do this with a single MySQL query as opposed to using program logic and two separate queries? — user1485133
– user1485133, Commented Jun 27, 2012 at 9:22
possible duplicate of Another SELECT if the first SELECT returned an empty set — Evan Carroll
– Evan Carroll, Commented Jul 2, 2013 at 1:46

Martin Smith · Accepted Answer · 2010-07-08 11:28:21Z

15

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.

edited Jul 8, 2010 at 11:28

answered Jul 8, 2010 at 9:46

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

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

2 Comments

Brian Hooper Over a year ago

Thank you for that, Mr Smith. I didn't know about SQL_CALC_FOUND_ROWS.

lurscher Over a year ago

the problem seems to be that FOUND_ROWS() will return a value for the next executed query, the UNION ALL considers both queries to be the same

Brian Hooper · Accepted Answer · 2010-07-08 09:37:01Z

1

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.

answered Jul 8, 2010 at 9:37

Brian Hooper

22.2k25 gold badges91 silver badges146 bronze badges

Comments

lurscher · Accepted Answer · 2018-02-12 19:30:07Z

1

SQL_CALC_FOUND_ROWS and FOUND_ROWS cannot be used in a single query, even if separate by UNION statements.

The correct way to do this would be:

WITH  my_cte AS
(
  SELECT * from original_set
)
SELECT * FROM my_cte
UNION ALL
SELECT opt.* FROM optional_set opt JOIN (SELECT count(*) v FROM my_cte) count ON count.v=0;

With the JOIN and the UNION ALL the performance of this query is almost equivalent to either of the individual standalone queries

answered Feb 12, 2018 at 19:30

lurscher

27.2k30 gold badges131 silver badges196 bronze badges

Comments

Haim Evgi · Accepted Answer · 2010-07-08 10:19:28Z

0

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

edited Jul 8, 2010 at 10:19

answered Jul 8, 2010 at 9:35

Haim Evgi

126k46 gold badges222 silver badges227 bronze badges

1 Comment

emre Over a year ago

will this query return the rows from (SELECT * FROM t2) it any results EXIST(S)? because I will be needing them in that case...

ceteras · Accepted Answer · 2010-07-08 11:34:50Z

0

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.

answered Jul 8, 2010 at 11:34

ceteras

3,3882 gold badges21 silver badges14 bronze badges

Comments

Ilia S. · Accepted Answer · 2016-02-24 15:20:57Z

0

The simplest explanation is that:

SELECT IF(1 = 2,'true','false'); --> false
SELECT IF(1 = 1,' true','false'); --> true
SELECT IF(1 = 2,' true','false'), IF(1 = 1,' true','false'); --> false | true

The 'if' statement give some functionality to selected values. The structure is something like this:

SELECT IF(<your statement>), ...<selected params>... FROM <your tables>

A great explanation can be found here.

answered Feb 24, 2016 at 15:20

Ilia S.

8878 silver badges23 bronze badges

Comments

hassan mirza · Accepted Answer · 2018-02-12 19:39:46Z

-1

you can use EXIST and NOT EXIST statement to check that result is null or not. if result is Null then you can get value from table2.

answered Feb 12, 2018 at 19:39

hassan mirza

1392 silver badges9 bronze badges

1 Comment

MD TAREQ HASSAN Over a year ago

And how is this an answer without showing SQL query?

Collectives™ on Stack Overflow

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

1st:

2nd:

7 Answers 7

2 Comments

Comments

Comments

1 Comment

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1st:

2nd:

7 Answers 7

2 Comments

Comments

Comments

1 Comment

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related