abdou / classroom
Back to SQL Tutorial

SQL Lesson 8: A Short Note on NULLs

Learn to handle NULL values in SQL queries with IS NULL and IS NOT NULL constraints.

SQL Lesson 8: A Short Note on NULLs

As promised in the last lesson, we are going to quickly talk about NULL values in an SQL database. It's always good to reduce the possibility of NULL values in databases because they require special attention when constructing queries, constraints (certain functions behave differently with null values) and when processing the results.

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 WHEREclause by using either the IS NULL or IS NOT NULL constraint.

SELECT
column, another_column, ...
FROM
mytable
WHERE
column IS/IS NOT NULL
AND/OR
another_condition
AND/OR
...;

Important Notes about NULL:

  • NULL ≠ 0: NULL is not the same as zero or empty string
  • NULL ≠ NULL: You cannot use = or != to compare with NULL
  • Use IS NULL: Always use IS NULL or IS NOT NULL for NULL comparisons
  • Functions: Most functions return NULL when given NULL input
  • Aggregates: COUNT, SUM, AVG ignore NULL values

Exercise

This exercise will be a sort of review of the last few lessons. We're using the same Employees andBuildings table from the last lesson, but we've hired a few more people, who haven't yet been assigned a building.

Notice how some employees now have NULL values in their building column, and some buildings have no employees assigned to them.

Table: buildings (Read-only)

Table: employees (Read-only)

Query results

Exercise 8 — Tasks

  1. Find the name and role of all employees who have not been assigned to a building
    Hint: The Building for the employee will have a NULL value.
  2. Find the names of the buildings that hold no employees

Solve all tasks to continue to the next lesson.

Progress0 of 2 tasks completed