Thứ Hai, Tháng Sáu 21, 2021
spot_img
Trang chủKiến ThứcHỏi ĐápCâu Lệnh Coalesce Là Gì - Sql: So Sánh Giữa Ifnull Và...

Câu Lệnh Coalesce Là Gì – Sql: So Sánh Giữa Ifnull Và Coalesce

Summary: in this tutorial, you will learn about the PostgreSQL COALESCE function that returns the first non-null argument. You will learn how to apply this function in SELECT statement to handle nullvalues effectively.

Đang xem: Coalesce là gì

PostgreSQL COALESCE function syntax

The syntax of the COALESCE function is as follows:

Code language: SQL (Structured Query Language) (sql)The COALESCE function accepts an unlimited number of arguments. It returns the first argument that is not null. If all arguments are null, the COALESCE function will return null.

The COALESCE function evaluates arguments from left to right until it finds the first non-null argument. All the remaining arguments from the first non-null argument are not evaluated.

The COALESCE function provides the same functionalityas NVL or IFNULL function provided by SQL-standard. MySQL has IFNULL function, while Oracle provides NVL function.

Xem thêm: Những Cách Thay Đổi Vận Số Phận, Cách Thay Đổi Vận Mệnh

See the following examples:

Code language: SQL (Structured Query Language) (sql)

*

Code language: SQL (Structured Query Language) (sql)

*

We often use the COLAESCE function to substitute a default value for null valueswhen we querying the data. For example, we want to display the excerptfrom a blog post, if the excerptis not provided, we can use the first 150 characters of the of the content of the post. To achieve this, we can use the COALESCE function as follows:

Code language: SQL (Structured Query Language) (sql)

*

If you look at the fourth row, you will notice that the net price of the product D is null which seemsnot correct. The issue is the discount of the product D is null, therefore when we take the null value to calculate the net price, PostgreSQL returns null.

The get the right price, we need to assume that if the discount is null, it is zero. Then we can use the COALESCE function as follows:

Code language: SQL (Structured Query Language) (sql)

*

Now the net price of product D is 500 because we use zero instead of null value when we calculated the net price.

Xem thêm: Cách Nấu Cháo Thịt Gà Thơm Ngon Cho Bé Ăn Dặm |Mami Đảm Đang

Besides using the COALESCE function, you can use the CASEexpressionto handle the null values in this case. See the following query that uses the CASEexpressionto achieve the same result above.

RELATED ARTICLES

BÌNH LUẬN

Vui lòng nhập bình luận của bạn
Vui lòng nhập tên của bạn ở đây

Most Popular

Recent Comments