2021-03-17

最新推荐文章于 2025-12-05 20:44:21 发布

原创最新推荐文章于 2025-12-05 20:44:21 发布 · 145 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql

SQL习题总结专栏收录该内容

11 篇文章

订阅专栏

本文通过具体案例介绍了如何使用SQL中的COALESCE函数来处理缺失数据。从填充单个字段开始，逐步展示如何为多个字段设置默认值，特别是数量和金额这类数值型字段。文章还演示了在不同场景下COALESCE函数的应用技巧。

Definition and Usage

The COALESCE() function returns the first non-null value in a list.

Syntax

COALESCE(val1, val2, …, val_n)

Run the query entered below in the SQL workspace to notice the row with missing data.

SELECT *
FROM accounts a
LEFT JOIN orders o
ON a.id = o.account_id
WHERE o.total IS NULL;

Use COALESCE to fill in the accounts.id column with the account.id for the NULL value for the table in 1.

SELECT COALESCE(a.id, a.id) filled_id, a.name, a.website, a.lat, a.long, a.primary_poc, a.sales_rep_id, o.*
FROM accounts a
LEFT JOIN orders o
ON a.id = o.account_id
WHERE o.total IS NULL;

Use COALESCE to fill in the orders.account_id column with the account.id for the NULL value for the table in 1.

SELECT COALESCE(a.id, a.id) filled_id, a.name, a.website, a.lat, a.long, a.primary_poc, a.sales_rep_id, COALESCE(o.account_id, a.id) account_id, o.occurred_at, o.standard_qty, o.gloss_qty, o.poster_qty, o.total, o.standard_amt_usd, o.gloss_amt_usd, o.poster_amt_usd, o.total_amt_usd
FROM accounts a
LEFT JOIN orders o
ON a.id = o.account_id
WHERE o.total IS NULL;

Use COALESCE to fill in each of the qty and usd columns with 0 for the table in 1.

SELECT COALESCE(a.id, a.id) filled_id, a.name, a.website, a.lat, a.long, a.primary_poc, a.sales_rep_id, COALESCE(o.account_id, a.id) account_id, o.occurred_at, COALESCE(o.standard_qty, 0) standard_qty, COALESCE(o.gloss_qty,0) gloss_qty, COALESCE(o.poster_qty,0) poster_qty, COALESCE(o.total,0) total, COALESCE(o.standard_amt_usd,0) standard_amt_usd, COALESCE(o.gloss_amt_usd,0) gloss_amt_usd, COALESCE(o.poster_amt_usd,0) poster_amt_usd, COALESCE(o.total_amt_usd,0) total_amt_usd
FROM accounts a
LEFT JOIN orders o
ON a.id = o.account_id
WHERE o.total IS NULL;

Run the query in 1 with the WHERE removed and COUNT the number of ids.

SELECT COUNT(*)
FROM accounts a
LEFT JOIN orders o
ON a.id = o.account_id;

Run the query in 5, but with the COALESCE function used in questions 2 through 4.

SELECT COALESCE(a.id, a.id) filled_id, a.name, a.website, a.lat, a.long, a.primary_poc, a.sales_rep_id, COALESCE(o.account_id, a.id) account_id, o.occurred_at, COALESCE(o.standard_qty, 0) standard_qty, COALESCE(o.gloss_qty,0) gloss_qty, COALESCE(o.poster_qty,0) poster_qty, COALESCE(o.total,0) total, COALESCE(o.standard_amt_usd,0) standard_amt_usd, COALESCE(o.gloss_amt_usd,0) gloss_amt_usd, COALESCE(o.poster_amt_usd,0) poster_amt_usd, COALESCE(o.total_amt_usd,0) total_amt_usd
FROM accounts a
LEFT JOIN orders o
ON a.id = o.account_id;