带空值的日期比较：如何在SQL查询中忽略Null值？

Hey there! Let's get your query to return exactly the rows you want—IDs 1 and 3. The issue right now is that when EndDate is NULL, the condition a.StateDate <= EndDate evaluates to unknown, so the WHERE clause filters out those rows automatically. Here are two straightforward ways to adjust your query:

Option 1: Explicitly Check for NULL Values #

Add an OR condition to include rows where EndDate is NULL along with the valid date comparison:

SELECT * 
FROM tablea a 
WHERE 1=1 
  AND (a.StateDate <= a.EndDate OR a.EndDate IS NULL);

How this works: #

• For ID 1: 01/01/2018 <= 01/30/2018 is true, so the row is included.
• For ID 2: 02/15/2018 <= 02/01/2018 is false, and EndDate isn't NULL—so this row gets excluded.
• For ID 3: EndDate IS NULL is true, so the row is included.

Option 2: Use COALESCE to Replace NULL with a Large Date #

If you prefer a more concise condition, use COALESCE to replace NULL EndDate values with a far-future date (something guaranteed to be later than any possible StateDate):

SELECT * 
FROM tablea a 
WHERE 1=1 
  AND a.StateDate <= COALESCE(a.EndDate, '9999-12-31');

How this works: #

• COALESCE returns the first non-NULL value in its arguments. For rows with a NULL EndDate, it uses '9999-12-31'—a date so far in the future that any StateDate will always be less than or equal to it. This keeps ID 3's row while still filtering out ID 2's invalid date range.

Either of these approaches will give you the exact results you're looking for!

内容的提问来源于stack exchange，提问作者John