Geek Answers Handbook

MYSQL: how to undo "IS NULL" in a WHERE clause

I think I'm banging my head about it 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 only give those that have NULL if 3 is not found. How can I hold strings that are NULL if 3 is found, and give me strings instead with NULL if 3 is not found?

Is there a way to do this in a single request? I also tried XOR, which will give my only lines where 3 is found, but not if not.

+4
source share
1 answer

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 the connection:

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
+5
source

More articles:


All Articles