COALESCE trong SQL Server

Trong hướng dẫn này, bạn sẽ học cách sử dụng biểu thức COALESCE trong SQL Server để xử lý các giá trị NULL trong các truy vấn.

Giới thiệu về biểu thức COALESCE trong SQL Server

Biểu thức COALESCE trong SQL Server chấp nhận một tập các đối số, đánh giá chúng theo trình tự và trả về đối số không rỗng đầu tiên.

Sau đây minh họa cú pháp của biểu thức COALESCE:

COALESCE(e1,[e2,...,en])

Trong cú pháp này, e1, e2,… en là các biểu thức vô hướng trả về các giá trị vô hướng (scalar value). Biểu thức COALESCE trả về biểu thức không NULL đầu tiên. Nếu tất cả các biểu thức đánh giá là NULL, thì biểu thức COALESCE trả về NULL;

Bởi vì COALESCE là một biểu thức, bạn có thể sử dụng nó trong bất kỳ mệnh đề chấp nhận một biểu thức như SELECT, WHERE, GROUP BY và HAVING.

Ví dụ về biểu thức COALESCE trong SQL Server

Hãy xem các ví dụ thực tế về việc sử dụng biểu thức COALESCE:

Sử dụng biểu thức COALESCE trong SQL Server với dữ liệu chuỗi ký tự

Ví dụ sau sử dụng biểu thức COALESCE để trả về chuỗi 'Hi' vì nó là đối số không rỗng đầu tiên:

SELECT 
    COALESCE(NULL, 'Hi', 'Hello', NULL) result;

Đây là đầu ra:

result
------
Hi

(1 row affected)

Sử dụng biểu thức COALESCE trong SQL Server với dữ liệu số

Ví dụ này sử dụng biểu thức COALESCE để đánh giá danh sách các đối số và trả về số đầu tiên:

SELECT 
    COALESCE(NULL, NULL, 100, 200) result;

Kết quả như sau:

result
-----------
100

(1 row affected)

Sử dụng biểu thức COALESCE trong SQL Server để thay thế NULL bằng các giá trị mới

Xem bảng sales.customers sau từ cơ sở dữ liệu mẫu.

Truy vấn sau trả về tên, họ, điện thoại và email của tất cả khách hàng:

SELECT 
    first_name, 
    last_name, 
    phone, 
    email
FROM 
    sales.customers
ORDER BY 
    first_name, 
    last_name;

Đây là kết quả một phần:

Cột phone sẽ có giá trị NULL nếu khách hàng không có số điện thoại ghi trong bảng sales.customers.

Để làm cho đầu ra thân thiện hơn với doanh nghiệp, bạn có thể sử dụng biểu thức COALESCE để thay thế giá trị NULL bằng chuỗi N/A (không có sẵn) như được hiển thị trong truy vấn sau:

SELECT 
    first_name, 
    last_name, 
    COALESCE(phone,'N/A') phone, 
    email
FROM 
    sales.customers
ORDER BY 
    first_name, 
    last_name;

Hình ảnh sau đây cho thấy đầu ra một phần:

Sử dụng biểu thức COALESCE trong SQL Server để sử dụng dữ liệu có sẵn

Đầu tiên, hãy tạo một bảng mới có tên salaries để lưu trữ thông tin lương của nhân viên:

CREATE TABLE salaries (
    staff_id INT PRIMARY KEY,
    hourly_rate decimal,
    weekly_rate decimal,
    monthly_rate decimal,
    CHECK(
        hourly_rate IS NOT NULL OR 
        weekly_rate IS NOT NULL OR 
        monthly_rate IS NOT NULL)
);

Mỗi nhân viên chỉ có thể có một suất theo giờ, hàng tuần hoặc hàng tháng.

Thứ hai, chèn một số hàng vào bảng salaries:

INSERT INTO 
    salaries(
        staff_id, 
        hourly_rate, 
        weekly_rate, 
        monthly_rate
    )
VALUES
    (1,20, NULL,NULL),
    (2,30, NULL,NULL),
    (3,NULL, 1000,NULL),
    (4,NULL, NULL,6000);
    (5,NULL, NULL,6500);

Thứ ba, truy vấn dữ liệu từ bảng salaries:

SELECT
    staff_id, 
    hourly_rate, 
    weekly_rate, 
    monthly_rate
FROM
    salaries
ORDER BY
    staff_id;

Đây là đầu ra:

Thứ tư, tính toán hàng tháng cho mỗi nhân viên bằng cách sử dụng biểu thức COALESCE như được hiển thị trong truy vấn sau:

SELECT
    staff_id,
    COALESCE(
        hourly_rate*22*8, 
        weekly_rate*4, 
        monthly_rate
    ) monthly_salary
FROM
    salaries;

Hình ảnh sau đây cho thấy kết quả đầu ra:

Trong ví dụ này, chúng tôi sử dụng biểu thức COALESCE để trả về tập kết quả chỉ có giá trị không NULL tìm thấy trong cột hourly_rate, weekly_rate và monthly_rate.

Biểu thức COALESCE so với biểu thức CASE

Biểu thức COALESCE là một cú pháp thân thiện của biểu thức CASE.

Các biểu thức sau trả về cùng một kết quả:

COALESCE(e1,e2,e3)

CASE
    WHEN e1 IS NOT NULL THEN e1
    WHEN e2 IS NOT NULL THEN e2
    ELSE e3
END

Lưu ý rằng trình tối ưu hóa truy vấn có thể sử dụng biểu thức CASE để viết lại biểu thức COALESCE.

Trong hướng dẫn này, bạn đã học cách sử dụng biểu thức COALESCE trong SQL Server để xử lý các giá trị NULL trong các truy vấn.

SQL Server