MySQL -- using only SQL, how to return the results of an alternate query if the current query returned empty?
Suppose we have this person table
id name gender
1 John male
2 Kurt male
3 Tyler male
4 Matthias开发者_JS百科 male
And we have this query
SELECT * FROM person WHERE gender = female
which would return empty
Is there a way in MySQL(inside the SQL) to run and return the results of an alternate query if the current query returned empty?
Something like "if there are no rows returned, return rows with gender = male instead"
Note that this only works in stored procedures and functions because of IF THEN / ELSE
statement disposal.
IF EXISTS (SELECT 0 FROM person WHERE gender = female) THEN
SELECT * FROM person WHERE gender = female
ELSE
SELECT * FROM person WHERE gender = male
http://dev.mysql.com/doc/refman/5.0/en/if-statement.html
http://dev.mysql.com/doc/refman/5.0/en/exists-and-not-exists-subqueries.html
精彩评论