Menu

COALESCE, NULLIF và CASE WHEN trong PostgreSQL

COALESCE(a, b, ...) trả về đối số đầu tiên khác NULL, NULLIF(a, b) biến một giá trị thành NULL, và CASE WHEN chọn một giá trị theo điều kiện.

Trang này có trình soạn thảo chạy trực tiếp. Sửa, chạy và xem đầu ra ngay lập tức.

COALESCE(a, b, c) trả về đối số đầu tiên khác NULL, nên COALESCE(nickname, name) hiện biệt danh khi có và hiện tên khi không có. NULLIF(a, b) làm ngược lại: nó trả về NULL khi a bằng b. CASE WHEN condition THEN value ... ELSE value END chọn một giá trị theo điều kiện.

Ben và Chen không có biệt danh, nên tên đầy đủ của họ được hiển thị. Ben còn nhận no phone.

Thay NULL bằng giá trị mặc định

COALESCE chính là IFNULL, ISNULL và NVL của PostgreSQL. Dùng nó để biến NULL thành 0 trước khi tính toán, vì mọi phép tính với NULL đều cho ra NULL, và để cho một hàm tổng hợp trên tập rỗng có giá trị:

Đơn hàng 2 hiện NULL khi không có COALESCE và 80 khi có, còn tổng trên tập không có hàng nào trở thành 0 thay vì NULL.

Mọi đối số phải quy về cùng một kiểu. COALESCE(quantity, 'none') trên một cột số nguyên báo lỗi "invalid input syntax for type integer", vì PostgreSQL cố đọc 'none' như một số nguyên. Hãy ép kiểu cột trước: COALESCE(quantity::text, 'none'). Trang CAST nói về ép kiểu.

Biến một giá trị thành NULL với NULLIF

NULLIF(a, b) trả về NULL nếu a = b, và trả về a trong trường hợp còn lại. Nó giúp tránh chia cho 0, và biến chuỗi rỗng thành NULL để COALESCE thay thế được:

spring có tỷ lệ nhấp là 5. summer nhận NULL thay vì lỗi "division by zero", và người phụ trách trống của nó trở thành unassigned.

Chọn giá trị với CASE WHEN

Dạng này kiểm tra các điều kiện theo thứ tự và trả về kết quả khớp đầu tiên. Những hàng không khớp điều kiện nào nhận giá trị của ELSE, hoặc NULL khi không có ELSE:

CASE trong ORDER BY đẩy các sản phẩm đã hết hàng xuống cuối, nên mug đứng cuối cùng. Một dạng ngắn hơn so sánh một biểu thức với các giá trị: CASE status WHEN 'paid' THEN 1 WHEN 'refunded' THEN -1 ELSE 0 END. Nó không kiểm tra được NULL, vì NULL = NULL không đúng. Khi đó hãy dùng WHEN x IS NULL ở dạng đầy đủ.

Đếm theo điều kiện

CASE bên trong một hàm tổng hợp đếm hoặc cộng theo từng nhóm chỉ trong một lần quét. PostgreSQL còn có mệnh đề FILTER, diễn đạt cùng ý đó trực tiếp hơn:

SELECT count(CASE WHEN status = 'paid' THEN 1 END) AS paid,
       count(*) FILTER (WHERE status = 'paid')      AS paid_too
FROM orders;

Cả hai chỉ đếm các đơn hàng đã thanh toán: count() bỏ qua NULL mà CASE trả về cho những hàng khác.

GREATEST và LEAST bỏ qua NULL

GREATEST(a, b, ...) và LEAST(a, b, ...) trả về đối số lớn nhất và nhỏ nhất. Trong PostgreSQL, chúng bỏ qua các đối số NULL và chỉ trả về NULL khi mọi đối số đều là NULL, nên GREATEST(1, NULL, 3) là 3. MySQL trả về NULL khi có bất kỳ đối số nào là NULL, điều này quan trọng khi bạn chuyển truy vấn. Xem tài liệu PostgreSQL về biểu thức điều kiện để biết đầy đủ các quy tắc.

Câu hỏi thường gặp

Hàm nào trong PostgreSQL tương đương IFNULL, ISNULL hay NVL?

COALESCE(value, fallback). PostgreSQL không có hàm IFNULL (MySQL), ISNULL (SQL Server) hay NVL (Oracle); COALESCE là SQL chuẩn và còn nhận được nhiều hơn hai đối số.

Làm sao thay NULL bằng 0 trong PostgreSQL?

Bọc cột lại: COALESCE(discount, 0). Với phép cộng trên tập không có hàng nào, vốn trả về NULL, hãy viết COALESCE(SUM(amount), 0).

COALESCE có tính tất cả các đối số không?

Không. Nó tính các đối số theo thứ tự và dừng ở đối số đầu tiên khác NULL, nên các đối số sau không được tính cho hàng đó.

Có dùng CASE trong mệnh đề WHERE được không?

Được, biểu thức CASE có thể xuất hiện ở bất kỳ chỗ nào đặt được một giá trị, kể cả WHERE, ORDER BY và bên trong hàm tổng hợp. Trong WHERE, một điều kiện AND/OR thông thường thường dễ đọc hơn.

Hình minh họa các ngôn ngữ lập trình của Coddy

Học lập trình cùng Coddy

Bắt đầu