GREATEST() 和 LEAST() 函數還有 NULL

今天我們要 dive 更細一點的主題：GREATEST() 跟 LEAST() 這兩個函數。你會學到怎麼從多個欄位找出最大值和最小值，還有最重要的，NULL 到底會怎麼影響它們的行為。

如果你曾經在生活中找過什麼最重要的東西（比如愛情、夢想工作或是最強的 pizza 食譜），你應該馬上就能懂 GREATEST() 跟 LEAST() 的用法。這兩個函數就是幫你從一堆 東西 裡面挑出最大或最小的。只是這裡我們不是比 pizza，而是比數字、日期、字串或其他 PostgreSQL 裡的資料。

GREATEST()

GREATEST() 會回傳你給它的參數裡面最大的那個值。

語法：

GREATEST(value1, value2, ..., valueN)

LEAST()

LEAST() 則是反過來：它會找出最小的值。

語法：

LEAST(value1, value2, ..., valueN)

範例：

假設我們有一張 students_scores 表，裡面存著學生三次考試的分數：

來用一下 GREATEST() 跟 LEAST()：

SELECT
    student_id,
    GREATEST(exam_1, exam_2, exam_3) AS highest_score,
    LEAST(exam_1, exam_2, exam_3) AS lowest_score
FROM students_scores;

結果：

NULL 怎麼影響 GREATEST() 跟 LEAST()

現在來到最有趣的地方。表格裡除了有數值，也可能有 NULL。 你應該知道 NULL 就是神祕的存在，代表沒資料或不知道的值。 來看看如果 NULL 跑進 GREATEST() 跟 LEAST() 會怎樣。

NULL 的行為：

在 PostgreSQL 裡，GREATEST() 跟 LEAST() 有個特別的行為：它們在找最大或最小值時會 忽略 NULL。 重點： 只有一種情況會回傳 NULL，就是 全部 參數都是 NULL。

範例：

SELECT
    GREATEST(10, 20, NULL, 5) AS greatest_value,
    LEAST(10, 20, NULL, 5) AS least_value;

結果：

你看，NULL 被直接無視掉，函數只看剩下的 (10, 20, 5) 去找最大最小。

如果全部參數都是 NULL，就會這樣：

範例：

SELECT
    GREATEST(NULL, NULL) AS greatest_nulls,
    LEAST(NULL, NULL) AS least_nulls;

結果：

怎麼避免 NULL 帶來的麻煩？

雖然 PostgreSQL 預設會忽略 NULL，但有時你可能想要不一樣的行為。比如你想讓 NULL 當成某個具體的值（像 0 或其他預設值）來算最大/最小。這時就可以用 COALESCE()。

COALESCE(arg1, arg2, ...) 這個函數會回傳第一個不是 NULL 的參數。這樣你就能在丟進 GREATEST() 或 LEAST() 之前，先把 NULL 換成有意義的值。

範例 1：把 NULL 換成 0

假設我們想把沒分數（NULL）當成 0。可以用 COALESCE() 來補預設值。

這是原始表格：

查詢：

SELECT
    student_id,
    GREATEST(
        COALESCE(exam_1, 0), 
        COALESCE(exam_2, 0), 
        COALESCE(exam_3, 0)
    ) AS highest_score,
    LEAST(
        COALESCE(exam_1, 0), 
        COALESCE(exam_2, 0), 
        COALESCE(exam_3, 0)
    ) AS lowest_score
FROM students_scores;

結果：

範例 2：把 NULL 換成其他欄位的值

有時候你不想用固定值（像 0），而是想用別的欄位來補。比如 exam_3 沒分數時，就用 exam_1 的分數。

SELECT
    student_id,
    GREATEST(
        exam_1, 
        exam_2, 
        COALESCE(exam_3, exam_1)
    ) AS highest_score
FROM students_scores;

假設我們有這樣的表格：

查詢結果：

實戰案例

案例 1：找出最大折扣

你現在在處理 orders 這張表，每個訂單可能有三種不同的折扣。你要找出每個訂單裡最大的那個折扣。

SELECT
    order_id,
    GREATEST(discount_1, discount_2, discount_3) AS max_discount
FROM orders;

結果：

案例 2：找出商品最低價

在 products 表裡，每個商品有三種貨幣的價格（USD, EUR, GBP）。你的任務是找出每個商品的最低價。

SELECT
    product_id,
    LEAST(price_usd, price_eur, price_gbp) AS lowest_price
FROM products;

如果全部價格都是 NULL，結果也會是 NULL

用 GREATEST() 跟 LEAST() 常見錯誤

錯誤 1：因為 NULL 得到意外的結果。

我們前面有講過 NULL 在 PostgreSQL 裡對 GREATEST() 跟 LEAST() 的影響。常見的錯誤是，有些人習慣其他資料庫系統的 NULL 行為（只要有一個 NULL 就整個結果都是 NULL），所以以為 PostgreSQL 也會這樣。

錯誤怎麼發生： 你可能會以為只要參數裡有 NULL，函數就一定會回傳 NULL。結果你會不必要地對所有參數都用 COALESCE()，這樣反而讓查詢變複雜又慢，尤其如果你本來就想讓 NULL 被忽略。

錯誤 2：用 GREATEST() 跟 LEAST() 比不同型別的值。

GREATEST() 跟 LEAST() 設計來比同一種 資料型別 的值，或是可以自動轉換的型別。如果你硬要比完全不相容的型別，會直接報錯。

錯誤怎麼發生： 你會收到一個錯誤訊息，說資料型別不相容。