user1116377 user1116377 - 19 days ago 7
SQL Question

SQL - comparing date to inner select that might return null

I would like to get all events with start date greater than the start date of a given event id ( e.g. 12345).

I used the following query:

SELECT *
FROM Events
WHERE StartDate > (SELECT StartDate
FROM Events
WHERE ID = 12345)


my questions are:


  1. What happens if there is no event with id 12345?

  2. What happens if the event 12345 has no startDate i.e. has null value


Answer

What happens if there is no event with id 12345?

Nothing will be returned as the sub-query does not return any rows which in turn will be treated as a NULL value in the comparison.

You can however re-write the query to return something in that case. But without more information what you are trying to achieve it's hard to tell.

What happens if the event 12345 has no startDate i.e. has null value

Again, nothing will be returned because any comparison of a null value returns "undefined" which - when used as a condition in a where clause - essentially is the same as "false".

If you want, you could treat that null value with some default date though. Which default to choose totally depends on what you want. You could e.g. use a very early date, or today or a very late date

SELECT * 
FROM events 
WHERE startdate > (SELECT coalesce(startdate, sysdate)
                   FROM events 
                   WHERE id = 12345)