Khi công nghệ phát triển và phát triển, điều quan trọng đối với bạn với tư cách là nhà phát triển là phải luôn cập nhật các xu hướng mới nhất. Cho dù bạn là người mới bắt đầu hay chuyên gia, sự hiểu biết vững chắc về thao tác chuỗi sẽ giúp bạn chuẩn bị dữ liệu của mình (ví dụ: tạo một biểu mẫu khác với biểu mẫu hiện có để giúp nó có thể sử dụng được trong doanh nghiệp của bạn) và quản lý nó bằng máy chủ SQL tích hợp chức năng.
Ngoài thao tác dữ liệu, bạn có thể kiểm tra tập dữ liệu, đánh giá giá trị dữ liệu và mã hóa hoặc giải mã chúng để tạo ra dữ liệu có ý nghĩa hơn. Do đó, nó giúp bạn điều hướng các giá trị còn thiếu trong tập dữ liệu của mình, hiểu tác động của chúng đối với các phép tính và hợp lý hóa quy trình xử lý dữ liệu tổng thể của bạn để tránh các Null có thể làm hỏng kết quả hoạt động của bạn.
Hướng dẫn này sẽ đưa bạn qua tính năng hợp nhất trong SQL giúp bạn xây dựng các chương trình phức tạp. Bài viết giả định rằng bạn đã từng làm việc với SQL và chỉ muốn hiểu rõ hơn về chức năng cụ thể này. Chuỗi hướng dẫn SQL của chúng tôi sẽ giúp bạn bắt đầu nhanh chóng.
COALESCE() trong SQL là gì và cách sử dụng nó?
Hàm hợp nhất SQL đánh giá các tham số (đối số) theo một thứ tự cụ thể, chẳng hạn như danh sách và trả về giá trị không rỗng đầu tiên. Nói một cách đơn giản, hàm đánh giá danh sách một cách tuần tự và kết thúc ở giá trị khác null đầu tiên. Nếu tất cả đối số trong danh sách đều trống, hàm sẽ trả về NULL.
Ngoài ra, tính năng này còn được bao gồm và hỗ trợ trong các cơ sở dữ liệu khác như MYSQL, Azure SQL Database, Oracle và PostgreSQL.
Bạn có thể sử dụng Coalesce trong các trường hợp sau khi:
- Xử lý giá trị NULL.
- Chạy một số truy vấn dưới dạng một.
- Tránh các câu lệnh CASE dài dòng, tốn thời gian.
Khi được sử dụng thay cho câu lệnh CASE (hoặc hàm ISNULL), hàm kết hợp sẽ nhận nhiều tham số, không giống như câu lệnh CASE chỉ yêu cầu hai tham số. Cách tiếp cận này cho phép bạn viết ít mã hơn và làm cho quá trình viết dễ dàng hơn.
Đây là cú pháp:
COALESCE(valueOne, valueTwo, valueThree, …, valueX);
Một phép nối trong máy chủ SQL có một số thuộc tính, bao gồm các đối số của cùng loại dữ liệu chấp nhận nhiều tham số và các đối số số nguyên sẽ được hàm lợi nhuận xếp tầng để trả về một số nguyên làm kết quả.
Cũng đọc: Bảng tính SQL cơ bản để đánh dấu để sử dụng sau
Nhưng trước khi chúng ta bắt đầu sử dụng tính năng kết hợp, hãy hiểu NULL.
NULL trong SQL là gì?
Dấu NULL duy nhất trong SQL cho biết giá trị không tồn tại trong cơ sở dữ liệu. Bạn có thể coi nó như một giá trị không xác định hoặc không xác định. Xin đừng rơi vào cái bẫy nghĩ về nó như một chuỗi rỗng hoặc một giá trị null; không có giá trị. Sự xuất hiện của giá trị null trong các cột của bảng thể hiện thông tin bị thiếu.
Trên thực tế, cột dữ liệu trong cột cơ sở dữ liệu của trang thương mại điện tử có thể chứa NULL nếu khách hàng không cung cấp ID của họ. Null trong SQL là duy nhất; là một trạng thái, không giống như các ngôn ngữ lập trình khác, có nghĩa là “không trỏ đến một đối tượng cụ thể”.
Giá trị NULL trong SQL có tác động đáng kể đến cơ sở dữ liệu quan hệ. Đầu tiên, chúng cho phép bạn loại trừ các giá trị cụ thể khi làm việc với các chức năng nội bộ khác. Ví dụ: bạn có thể tạo danh sách tất cả các đơn hàng trong môi trường sản xuất, nhưng những đơn hàng khác vẫn cần được thực hiện. Việc sử dụng NULL làm phần giữ chỗ cho phép hàm SUM bên trong cộng tổng.
Ngoài ra, hãy xem xét các trường hợp bạn cần tạo mức trung bình bằng AVG. Nếu bạn đang làm việc với các giá trị null thì kết quả sẽ bị sai lệch. Thay vào đó, cơ sở dữ liệu có thể loại bỏ các trường như vậy và sử dụng NULL để mang lại kết quả chính xác.
Giá trị NULL không có nhược điểm. Chúng được coi là các giá trị có độ dài thay đổi, là byte hoặc vài byte. Vì cơ sở dữ liệu để lại không gian cho các byte này nếu chúng vượt quá số lượng được lưu trữ trong cơ sở dữ liệu nên kết quả là cơ sở dữ liệu chiếm nhiều dung lượng đĩa cứng hơn các giá trị bình thường.
Ngoài ra, khi làm việc với một số hàm, bạn cần điều chỉnh chúng để loại bỏ NULL. Kết quả là điều này kéo dài các thủ tục SQL.
Xử lý NULL bằng COALESCE()
Giá trị null có nghĩa là bạn có thể có một giá trị nhưng bạn không biết nó phải là gì. Cho đến khi bạn thu thập dữ liệu lấp đầy các trường của mình bằng các giá trị thực, các giá trị NULL sẽ là giá trị chính.
Mặc dù các giá trị NULL có thể được sử dụng trong cơ sở dữ liệu cho nhiều loại dữ liệu, bao gồm số thập phân, chuỗi, đốm màu và số nguyên, nhưng bạn nên tránh sử dụng chúng cho dữ liệu số.
Nhược điểm là khi sử dụng cho các giá trị số, bạn có thể cần làm rõ một số điều khi phát triển mã hoạt động với dữ liệu. Thêm về điều đó sau.
Các cách khác nhau của COALESCE() có thể được sử dụng để xử lý các giá trị NULL:
Sử dụng COALESCE() để thay thế giá trị null bằng một giá trị cụ thể
Bạn có thể sử dụng COALESCE() để trả về các giá trị cụ thể cho tất cả các giá trị null. Ví dụ: bạn có thể có một bảng có tên là “nhân viên” với cột “mức lương” có thể chứa giá trị null nếu khoản bồi thường của nhân viên chưa được đăng. Vì vậy, khi thực hiện một số phép tính, bạn có thể muốn làm việc với một giá trị cụ thể, trong trường hợp này là 0, cho tất cả các mục nhập NULL. Đây là cách thực hiện.
SELECT COALESCE(salary, 0) AS adjusted_salary FROM employees;
Sử dụng COALESCE() để chọn giá trị không null đầu tiên từ nhiều tùy chọn
Đôi khi bạn có thể muốn làm việc với các giá trị không phải NULL đầu tiên trong danh sách biểu thức. Trong những trường hợp này, bạn thường có nhiều cột chứa dữ liệu liên quan và bạn muốn ưu tiên các giá trị không phải NULL của chúng. Cú pháp vẫn còn.
COALESCE (expression1, expression2, …)
Trong thực tế, giả sử bạn có một bảng liên hệ với các cột Prefer_name và full_name. Và bạn muốn tạo danh sách liên hệ bên cạnh tên ưa thích của họ (nếu có) hoặc tên đầy đủ của họ. Đây là cách để đối phó với nó.
SELECT COALESCE(preferred_name, full_name) AS display_name FROM contacts.
Nếu tên ưa thích không phải là NULL cho trường hợp thử nghiệm này, nó sẽ được trả về. Nếu không, tên đầy đủ sẽ được trả về dưới dạng tên hiển thị.
Nối chuỗi bằng cách kết hợp SQL
Bạn có thể gặp phải sự cố SQL khi nối chuỗi nếu có liên quan đến giá trị rỗng. Trong những trường hợp này, NULL được trả về là kết quả không mong muốn. Bây giờ NULL không phải là kết quả mong muốn của chúng ta, bạn có thể giải quyết vấn đề bằng cách sử dụng hàm kết hợp. Dưới đây là một ví dụ.
Việc nối chuỗi đơn giản được thực hiện bằng cách:
SELECT ‘Hello, where are you, ‘|| ‘John ’||? AS example
Mã trả về:
Ví dụXin chào, bạn đang ở đâu, John?
Tuy nhiên, nếu bạn sử dụng NULL như hình dưới đây:
SELECT ‘Hello, where are you, ‘ || null || ‘?’ AS example
Bây giờ là lối ra.
Vì bất kỳ phép nối nào của chuỗi văn bản chứa giá trị NULL đều trả về giá trị NULL, nên kết quả trên là NULL. Tuy nhiên, vấn đề được giải quyết bằng hàm kết hợp(). Khi sử dụng hàm này, bạn trả về một chuỗi trống (hoặc khoảng trắng) thay vì NULL. Ví dụ: giả sử bạn liệt kê tên các loại ô tô cùng với nhà sản xuất của chúng; đây là truy vấn của bạn
SELECT car || ‘, manufacturer: ‘ || COALESCE(manufacturer, ‘—') AS car_brand FROM stock
Nếu nhà sản xuất là NULL, dấu “-” sẽ xuất hiện thay vì NULL. Dưới đây là kết quả mong đợi.
car_brandoutlander, nhà sản xuất: -thúc đẩy bay, nhà sản xuất: Vận động viên Bentleyroyal, nhà sản xuất: -royal sedan, nhà sản xuất: Crown
Như bạn có thể thấy, kết quả NULL bị loại bỏ, kèm theo tùy chọn chèn giá trị chuỗi thay thế.
Kết hợp và xoay vòng SQL
Xoay vòng SQL là một kỹ thuật được sử dụng để chuyển đổi các hàng thành cột. Nó cho phép bạn chuyển đổi (xoay) dữ liệu từ dạng “chuẩn hóa” (có nhiều hàng và ít cột hơn) sang dạng “chuẩn hóa” (ít hàng hơn và nhiều cột hơn). Tính năng kết hợp có thể được sử dụng với tính năng xoay vòng SQL để xử lý các giá trị null trong kết quả được xoay vòng.
Khi bạn thực hiện PIVOT trong SQL, hãy chuyển đổi hàng thành cột; cột kết quả là hàm tổng hợp của một số dữ liệu. Nếu trong mọi trường hợp, tập hợp trả về giá trị null cho một ô cụ thể, bạn có thể sử dụng hàm `COALESCE` để thay thế các giá trị null bằng giá trị mặc định hoặc biểu diễn có ý nghĩa. Dưới đây là một ví dụ.
Hãy xem xét một bảng Doanh số bán hàng với các cột Năm, Quý và Doanh thu và bạn muốn xoay vòng dữ liệu; để bạn có các năm dưới dạng cột và tổng doanh thu cho mỗi quý dưới dạng giá trị. Tuy nhiên, một số quý không bao gồm dữ liệu doanh thu, dẫn đến kết quả tổng hợp có giá trị bằng 0. Trong trường hợp đó, bạn có thể sử dụng COALESCE để thay thế các giá trị rỗng trong kết quả bằng số 0 nổi (0).
SELECT
year,
COALESCE(SUM(CASE WHEN quarter="Q1" THEN revenue END), 0) AS Q1_Revenue,
COALESCE(SUM(CASE WHEN quarter="Q2" THEN revenue END), 0) AS Q2_Revenue,
COALESCE(SUM(CASE WHEN quarter="Q3" THEN revenue END), 0) AS Q3_Revenue,
COALESCE(SUM(CASE WHEN quarter="Q4" THEN revenue END), 0) AS Q4_Revenue
FROM sales
GROUP BY year;Hàm vô hướng do người dùng định nghĩa và hàm kết hợp SQL
Bạn có thể sử dụng UDF vô hướng và kết hợp để thực hiện logic phức tạp xử lý các giá trị rỗng. Việc kết hợp các hàm này sẽ giúp bạn đạt được các phép tính và chuyển đổi dữ liệu phức tạp hơn trong các truy vấn SQL. Hãy xem xét bảng Nhân viên có cấu trúc này.
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Salary INT,
Bonus INT
);Bạn có thể muốn tính tổng thu nhập của mỗi nhân viên (tiền lương cộng với tiền thưởng). Tuy nhiên, một số giá trị bị thiếu. Trong trường hợp này, UDF vô hướng của bạn có thể xử lý việc thêm lương và tiền thưởng trong khi việc hợp nhất xử lý các giá trị rỗng. Đây là UDF vô hướng của tổng thu nhập.
CREATE FUNCTION dbo.CalculateTotalEarnings (@salary INT, @bonus INT)
RETURNS INT
AS
BEGIN
DECLARE @totalEarnings INT;
SET @totalEarnings = @salary + COALESCE(@bonus, 0);
RETURN @totalEarnings;
END;
You can then use the scalar UDF with coalesce in a query:
SELECT EmployeeID, FirstName, LastName,
Salary, Bonus, dbo.CalculateTotalEarnings(Salary, Bonus) AS TotalEarnings
FROM Employees;Xác thực dữ liệu với SQL Coalesce
Khi làm việc với cơ sở dữ liệu, bạn có thể muốn xác thực các giá trị số. Ví dụ: giả sử bạn có các cột tên_sản phẩm, giá và giảm giá trong bảng sản phẩm. Bạn muốn tải tên sản phẩm, giá cả và mức giảm giá cho từng mặt hàng. Nhưng bạn muốn coi tất cả các giá trị giảm giá là NULL 0. Tính năng kết hợp có thể giúp ích. Đây là cách sử dụng nó.
SELECT product_name, price, COALESCE(discount, 0) AS discount FROM products
Kết hợp SQL và các cột được tính toán
Các cột được tính toán là các cột ảo được tính toán từ các biểu thức hoặc các cột khác trong bảng. Vì các cột được tính toán không được lưu trữ vật lý trong cơ sở dữ liệu nên chúng có thể được tận dụng bằng các phép nối khi xử lý các kịch bản và chuyển đổi phức tạp. Đây là một ví dụ sử dụng thực tế.
Hãy xem xét bảng “sản phẩm” có các cột “giá”, “chiết khấu” và “thuế suất”. Trong trường hợp này, bạn muốn tạo cột được tính “total_price” thể hiện giá cuối cùng của sản phẩm sau khi áp dụng chiết khấu và thuế. Nếu không chỉ định chiết khấu hoặc thuế (NULL), hãy tiếp tục tính toán bằng 0. Dưới đây là cách sử dụng sự kết hợp phù hợp với hoạt động của bạn.
CREATE TABLE products( price DECIMAL(10, 2), discount DECIMAL(10, 2), tax_rate DECIMAL(5, 2), total_price AS (COALESCE(price, 0) – COALESCE(price*discount, 0))* COALESCE(1+tax_rate, 1) );
Trong đoạn mã trên, đây là những gì sẽ xảy ra.
Cấu hình ở trên cho phép bạn tạo Total_price, một cột được tính toán với giá cuối cùng thực tế, mặc dù thiếu hoặc có giá trị NULL.
Kết hợp biểu thức SQL và CASE
Bạn có thể sử dụng cú pháp kết hợp thông qua biểu thức CASE. Đây là một ví dụ:
SELECT Productname + ‘ ’+ deliverydate productdetails, dealer, CASE WHEN cellphone is NOT NULL Then cellphone WHEN workphone is NOT NULL Then workphone ELSE ‘NA’ END EmergencyContactNumber FROM dbo.tb_EmergencyContact
Trong cấu hình ở trên, truy vấn CASE tương tự như hàm COALESCE.
Ngoài ra, có thể sử dụng các biểu thức COALESCE và CASE trong cùng một truy vấn. Hai kỹ thuật này có thể xử lý các giá trị NULL và áp dụng logic điều kiện cùng một lúc. Hãy minh họa điều này bằng một ví dụ.
Hãy xem xét trường hợp bạn có một bảng, các sản phẩm có cột sản phẩm_id, tên_sản phẩm, giá và chiết khấu. Một số sản phẩm của bạn có mức giảm giá cụ thể, trong khi những sản phẩm khác thì không. Nếu sản phẩm được giảm giá, bạn muốn hiển thị giá đã chiết khấu, nếu không thì nên hiển thị giá thông thường.
SELECT
product_id,
product_name,
price,
COALESCE(
CASE
WHEN discount > 0 THEN price - (price * discount / 100)
ELSE NULL
END,
price
) AS discounted_price
FROM products;
Trong đoạn mã trên, `CASE` kiểm tra xem `discount` có lớn hơn 0 hay không và tính giá chiết khấu, nếu không nó sẽ trả về NULL. Hàm `COALESCE` chấp nhận kết quả từ `CASE` và `price` làm tham số. Trả về giá trị không NULL đầu tiên, trả về giá chiết khấu nếu có hoặc giá bình thường nếu không có.
những từ cuối
Bài đăng này trình bày nhiều cách khác nhau để sử dụng hàm `COALESCE` trong các truy vấn cơ sở dữ liệu. Bằng cách đánh giá các tham số theo thứ tự cụ thể và trả về giá trị không NULL đầu tiên, việc kết hợp sẽ đơn giản hóa các truy vấn trong khi vẫn làm cho chúng hiệu quả.
Coalesce là một tính năng linh hoạt, cho dù bạn đang xử lý giá trị rỗng, nối chuỗi, xoay vòng dữ liệu, xác thực hay làm việc với các cột được tính toán. Bằng cách thành thạo việc kết hợp, các nhà phát triển có thể điều hướng dữ liệu bị thiếu và tạo ra các thiết kế cơ sở dữ liệu không có lỗi. Hãy chắc chắn để nắm vững kỹ thuật; bạn có thể cần thực hành chuyên sâu hơn.
Bây giờ bạn có thể kiểm tra cách tạo ràng buộc khóa ngoại trong SQL.
