NULL

Missing data is a common fact in the world of data management, processing and analysis for many reasons i.e. scripts and data collections processes may break, human error e.t.c. As such it may be useful to have a keyword to enable data retrieval with missing information in mind. The NULL keyword accomplishes just that.

IS NULL

IS NULL is commonly used to assess whether a row for a specific field has no value. Sometimes, you may want to know what or how many records in your database tables have missing data.

In our orders table, we requested our customers to input their information including ship_country, ship_via, freight. Some customers were not willing to do so. To check how many customers have not provided customer information, we can write a sample query like below and make use of the IS NULL operator.

select order_id, customer_id, ship_country, order_date
from orders
where freight IS NULL or ship_country IS NULL

| order_id | customer_id | ship_country | order_date |
| -------- | ----------- | ------------ | ---------- |
| 3022     | 2022        | null         | 2023-04-25 |
| 3024     | 2024        | Austria      | 2023-05-05 |
| 3027     | 2027        | null         | 2023-05-20 |
| 3029     | 2029        | Denmark      | 2023-06-01 |

The output will contain only the records whose freight information is not provided.

Missing data is almost unavoidable when you are working in the analytics or technical fields. The ability to identify and filter for them is the first step to determining how you decide to analyze the data knowing that there are missing observations that could change the scope or interpretation of your analysis.

In the next section, we look at the negation of IS NULL and what the output looks like.