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 BYHAVING.

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.

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

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:

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

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:

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

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_ratemonthly_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
Bài Viết Liên Quan:
Sequence trong SQL Server
Trung Nguyen 31/07/2021
Sequence trong SQL Server

Trong hướng dẫn này, bạn sẽ tìm hiểu về các đối tượng Sequence trong SQL Server để tạo ra một chuỗi các giá trị số dựa trên một đặc tả cụ thể.

Cột Identity trong SQL Server
Trung Nguyen 30/07/2021
Cột Identity trong SQL Server

Trong hướng dẫn này, bạn sẽ học cách sử dụng thuộc tính IDENTITY trong SQL Server để thêm cột định danh vào bảng.

CREATE TABLE trong SQL Server
Trung Nguyen 29/07/2021
CREATE TABLE trong SQL Server

Trong hướng dẫn này, bạn sẽ học cách sử dụng câu lệnh CREATE TABLE trong SQL Server để tạo một bảng mới.

Ràng buộc NOT NULL trong SQL Server
Trung Nguyen 28/07/2021
Ràng buộc NOT NULL trong SQL Server

Trong hướng dẫn này, bạn sẽ học cách sử dụng ràng buộc NOT NULL trong SQL Server để đảm bảo một cột không chứa dữ liệu NULL.