Oracle SQL如何在三个日期中取最早值并忽略NULL？

Got it, I totally get the frustration with Oracle's LEAST function here—its behavior of returning NULL as soon as any argument is NULL is super annoying when you just want to ignore missing values. Let's fix this without resorting to sketchy NVL workarounds that could introduce fake dates.

Method 1: Use a Subquery with MIN (Clean and Scalable) #

This approach unpivots your three date columns into rows, filters out NULLs, then takes the minimum of the remaining values. If all dates are NULL, the subquery returns NULL exactly like you want:

SELECT 
  (
    SELECT MIN(dt)
    FROM (
      SELECT DATE_1 AS dt FROM DUAL UNION ALL
      SELECT DATE_2 AS dt FROM DUAL UNION ALL
      SELECT DATE_3 AS dt FROM DUAL
    ) date_rows
    WHERE dt IS NOT NULL
  ) AS earliest_non_null_date
FROM MYTABLE;

How it works:

• The inner UNION ALL converts each column value into a separate row in a temporary result set.
• We filter out any NULL rows with WHERE dt IS NOT NULL.
• MIN(dt) grabs the earliest date from the non-NULL values. If there are no non-NULL values (all dates are NULL), MIN returns NULL naturally.

Method 2: Explicit CASE Statement (No Subqueries) #

If you prefer to avoid subqueries, you can write a detailed CASE statement that covers every possible combination of NULL/non-NULL values. This is more verbose but completely transparent in its logic:

SELECT
  CASE
    -- All three dates are NULL: return NULL
    WHEN DATE_1 IS NULL AND DATE_2 IS NULL AND DATE_3 IS NULL THEN NULL
    -- Only DATE_1 exists
    WHEN DATE_2 IS NULL AND DATE_3 IS NULL THEN DATE_1
    -- Only DATE_2 exists
    WHEN DATE_1 IS NULL AND DATE_3 IS NULL THEN DATE_2
    -- Only DATE_3 exists
    WHEN DATE_1 IS NULL AND DATE_2 IS NULL THEN DATE_3
    -- DATE_1 is NULL: compare DATE_2 and DATE_3
    WHEN DATE_1 IS NULL THEN LEAST(DATE_2, DATE_3)
    -- DATE_2 is NULL: compare DATE_1 and DATE_3
    WHEN DATE_2 IS NULL THEN LEAST(DATE_1, DATE_3)
    -- DATE_3 is NULL: compare DATE_1 and DATE_2
    WHEN DATE_3 IS NULL THEN LEAST(DATE_1, DATE_2)
    -- All dates are non-NULL: use normal LEAST
    ELSE LEAST(DATE_1, DATE_2, DATE_3)
  END AS earliest_non_null_date
FROM MYTABLE;

How it works:

• We handle every edge case explicitly, starting with the all-NULL scenario (returns NULL), then cases where only one date exists, then cases where one date is missing, and finally the normal case where all dates are present.
• No fake default dates are introduced—we only ever compare actual non-NULL dates from your table.

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