Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

In a simple query like

SELECT * 
FROM table
WHERE id='x' OR id='y'

How to give a priority to get only one of the WHERE clauses?

I mean getting rows with id='x'. Only if there is no row, get row with id='y'.

In other words, do not use id='y' if there is a row for id='x'.

share|improve this question

2 Answers 2

up vote 1 down vote accepted
SELECT * 
FROM   table
WHERE  id IN ('x', 'y')
AND    (id='x' OR NOT EXISTS(SELECT id FROM table WHERE id='x'))

should do the trick (caveat: I'm an MSSQL guy and haven't tried that in mySQL, but I suspect that syntax is accepted just fine there too) though the efficiency will depend on how many rows matching the first filtering clause there are (for every row matching id='x' OR id='y' he subquery in the EXISTS part will probably be run unless the query planner is bright enough to see that it depends on nothing in the outer query so can be run once and the result reused - if you are only expecting at most two (which with a column name like id I suspect is the case) this should not be an issue)

share|improve this answer

I'm not a MySQL guy either but believe that this does the trick.

SELECT SQL_CALC_FOUND_ROWS * 
FROM table
WHERE id='x' 
UNION ALL
SELECT * 
FROM table
WHERE FOUND_ROWS() = 0 AND id='y'

Disclaimer: I'm not sure whether this is documented/guaranteed behavior.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.