CODEWITHSUNDEEP
mysqlsql
A.M.P.
A.M.P.

Reputation: 65

MYSQL: How to fallback to 'IS NULL' in a 'WHERE' clause

I think I'm banging my head on this more than I should. I have this simple query:

SELECT * FROM opening_hours WHERE day_id = 3 OR day_id IS NULL

Which will give me all rows that have both, 3 and NULL in the day_id column, and it will give only the ones with NULL if 3 isn't found. How can I keep out the rows that have NULL if 3 is found, and give me instead the rows with NULL if 3 is not found?

Is there a way of doing this in one query? I've also tried XOR that will give my only the rows where 3 is found, but none if not.

Upvotes: 3

Views: 835

Answers (1)

eggyal
eggyal

Reputation: 125855

One possible way:

SELECT * FROM opening_hours WHERE CASE
  WHEN EXISTS(SELECT * FROM opening_hours WHERE day_id = 3) THEN day_id = 3
  ELSE day_id IS NULL
END

Another possible way:

SELECT * FROM opening_hours WHERE day_id <=> (
  SELECT   day_id
  FROM     opening_hours
  WHERE    day_id = 3 OR day_id IS NULL
  ORDER BY day_id DESC
  LIMIT    1
)

Or, using a join:

SELECT * FROM opening_hours NATURAL JOIN (
  SELECT MAX(day_id) AS day_id
  FROM   opening_hours
  WHERE  day_id = 3 OR day_id IS NULL
) t

Upvotes: 5

Related Questions