Hàm GREATEST() và LEAST() cùng với NULL

Hôm nay tụi mình sẽ đào sâu vào một chủ đề khá đặc biệt nhưng lại rất quan trọng: hàm GREATEST() và LEAST(). Bạn sẽ biết cách tìm giá trị lớn nhất và nhỏ nhất từ nhiều cột, và quan trọng nhất là NULL ảnh hưởng thế nào đến kết quả của chúng.

Nếu bạn từng đi tìm thứ quan trọng nhất trong đời mình (tình yêu, công việc mơ ước hay công thức pizza ngon nhất), bạn sẽ hiểu ngay tại sao cần dùng GREATEST() và LEAST(). Hai hàm này giúp bạn tìm ra giá trị lớn nhất hoặc nhỏ nhất trong một danh sách thứ gì đó. Chỉ khác là thay vì pizza, bạn làm việc với số, ngày tháng, chuỗi và các kiểu dữ liệu khác trong PostgreSQL.

GREATEST()

GREATEST() trả về giá trị lớn nhất trong tập giá trị truyền vào.

Cú pháp:

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

LEAST()

LEAST() làm điều ngược lại: nó tìm giá trị nhỏ nhất.

Cú pháp:

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

Ví dụ:

Giả sử tụi mình có bảng students_scores, nơi lưu điểm của sinh viên cho ba kỳ thi:

Cách dùng GREATEST() và 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;

Kết quả:

NULL ảnh hưởng thế nào đến GREATEST() và LEAST()

Giờ đến phần thú vị nhất. Trong bảng có thể xuất hiện cả giá trị NULL. Như tụi mình đã biết, NULL là một thứ bí ẩn, đại diện cho dữ liệu không tồn tại hoặc giá trị chưa biết. Cùng xem điều gì xảy ra nếu NULL xuất hiện trong các hàm GREATEST() và LEAST() trong PostgreSQL nhé.

Hành vi của NULL:

Trong PostgreSQL, các hàm GREATEST() và LEAST() có hành vi đặc biệt: chúng sẽ bỏ qua giá trị NULL khi tìm giá trị lớn nhất hoặc nhỏ nhất trong các tham số truyền vào. Lưu ý: Trường hợp duy nhất mà hai hàm này trả về NULL là khi tất cả các tham số đều là NULL.

Ví dụ:

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

Kết quả:

Nhìn nè, NULL đã bị bỏ qua, và các hàm trả về giá trị lớn nhất và nhỏ nhất trong số các giá trị còn lại (10, 20, 5).

Còn đây là ví dụ khi tất cả tham số đều là NULL:

Ví dụ:

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

Kết quả:

Làm sao tránh rắc rối với NULL?

Dù PostgreSQL mặc định bỏ qua NULL, đôi khi bạn lại muốn hành vi khác. Ví dụ, bạn muốn NULL được coi như một giá trị cụ thể (như 0 hoặc một giá trị mặc định khác) khi xác định giá trị lớn nhất/nhỏ nhất. Lúc này, bạn có thể dùng hàm COALESCE().

Hàm COALESCE(arg1, arg2, ...) trả về tham số đầu tiên không phải NULL trong danh sách. Nhờ đó, bạn có thể thay thế NULL bằng một giá trị hợp lý trước khi truyền vào GREATEST() hoặc LEAST().

Ví dụ 1: Thay NULL bằng 0

Giả sử bạn muốn coi điểm thiếu (NULL) là 0. Ta dùng COALESCE() để thay thế giá trị mặc định.

Bảng gốc của tụi mình:

Truy vấn:

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;

Kết quả:

Ví dụ 2: Thay NULL bằng giá trị từ cột khác

Đôi khi thay vì một giá trị cố định (như 0), bạn lại muốn lấy giá trị từ cột khác. Ví dụ, nếu exam_3 bị thiếu, bạn muốn dùng giá trị từ exam_1.

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

Giả sử bảng như sau:

Kết quả truy vấn:

Case thực tế

Case 1: Tìm giảm giá lớn nhất

Bạn đang làm việc với bảng orders, mỗi đơn hàng có thể có ba loại giảm giá khác nhau. Nhiệm vụ là tìm giảm giá lớn nhất cho từng đơn hàng.

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

Kết quả:

Case 2: Tìm giá thấp nhất của sản phẩm

Trong bảng products lưu giá sản phẩm ở ba loại tiền (USD, EUR, GBP). Nhiệm vụ của bạn là tìm giá thấp nhất cho từng sản phẩm.

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

Nếu tất cả giá đều NULL, kết quả cũng là NULL

Lỗi phổ biến khi dùng GREATEST() và LEAST()

Lỗi 1: Kết quả không như mong đợi do NULL.

Ở trên tụi mình đã nói kỹ về việc NULL ảnh hưởng thế nào đến GREATEST() và LEAST() trong PostgreSQL. Lỗi phổ biến là nhiều bạn quen với cách NULL hoạt động ở các hệ quản trị khác (nơi chỉ cần một NULL là cả kết quả thành NULL), nên cũng nghĩ PostgreSQL sẽ như vậy.

Lỗi xuất hiện thế nào: Bạn có thể nghĩ rằng nếu danh sách tham số có NULL, hàm sẽ luôn trả về NULL. Vì vậy, bạn có thể dùng COALESCE() cho tất cả tham số mà không cần thiết, làm truy vấn phức tạp và chậm hơn, trong khi thực ra NULL chỉ bị bỏ qua thôi.

Lỗi 2: Dùng GREATEST() và LEAST() với kiểu dữ liệu không tương thích.

Hai hàm GREATEST() và LEAST() chỉ nên dùng để so sánh các giá trị cùng kiểu dữ liệu hoặc các kiểu có thể tự động chuyển đổi cho nhau. Nếu bạn thử so sánh các kiểu hoàn toàn khác nhau, sẽ bị lỗi ngay.

Lỗi xuất hiện thế nào: Bạn sẽ nhận được thông báo lỗi về việc kiểu dữ liệu không tương thích.