SQLBolt：https://sqlbolt.com/lesson/introduction

• SQL Lesson 8: A short note on NULLs

使用概念 & 語法

• IS / IS NOT NULL

/* Select query with constraints on NULL values */
SELECT column, another_column, …
FROM mytable
WHERE column IS/IS NOT NULL
AND/OR another_condition
AND/OR …;

An alternative to NULL values in your database is to have data-type appropriate default values, like 0 for numerical data, empty strings for text data, etc. But if your database needs to store incomplete data, then NULL values can be appropriate if the default values will skew later analysis (for example, when taking averages of numerical data).

Sometimes, it's also not possible to avoid NULL values, as we saw in the last lesson when outer-joining two tables with asymmetric data. In these cases, you can test a column for NULL values in a WHERE clause by using either the IS NULL or IS NOT NULL constraint.

SQL Lesson 8: A short note on NULLs

本章提到了NULL的概念，用於表示資料庫中不存在任何的值。
雖然應該盡量避免使用NULL，而是去使用有意義的值，如數字 0 ， 空字串 " " 等，但還是有需要使用到的狀況，或是資料庫就是有缺失的情形。

以下我們開始解題：

題目 1

-- 1. Find the name and role of all employees who have not been assigned to a building
SELECT name, role
FROM employees
WHERE building IS NULL;

題目 2

-- 2. Find the names of the buildings that hold no employees
SELECT DISTINCT building_name
FROM buildings LEFT JOIN employees
ON building_name = building
WHERE role IS NULL;

講解完 NULL 的概念，明天我們就要進入 expression 表達式囉。

參考資料：

• SQL Lesson 8: A short note on NULLs
• 函數 SQL Functions （fooish 程式技術）