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

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 …;

Exercise

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

1. Find the name and role of all employees who have not been assigned to a building
	1. SELECT * FROM Employees WHERE Building IS NULL;
	2. SELECT Name, Role FROM Employees WHERE Building IS NULL;
2. Find the names of the buildings that hold no employees
	1. SELECT * FROM Buildings B Left JOIN Employees E ON B.Building_name = E.Building WHERE Role IS NULL;
	2. SELECT Building_name FROM Buildings B Left JOIN Employees E ON B.Building_name = E.Building WHERE Role IS NULL;
	3. SELECT B.Building_name FROM Employees E RIGHT JOIN Buildings B ON B.Building_name = E.Building WHERE E.Role IS NULL;
	4. SELECT Building_name FROM Buildings B WHERE NOT EXISTS ( SELECT 1 FROM Employees E WHERE E.Building = B.Building_name );
	5. SELECT Building_name FROM Buildings WHERE Building_name NOT IN ( SELECT DISTINCT Building FROM Employees WHERE Building IS NOT NULL );

Exercise 8 — Tasks

1. Find the name and role of all employees who have not been assigned to a building ✓
2. Find the names of the buildings that hold no employees ✓

This page is from SQLBolt's interactive tutorial, specifically "Lesson 8: A short note on NULLs." It discusses the concept of NULL values in SQL databases, explaining:

• NULL values represent missing or unknown data. It's best to minimize their use, but sometimes they are necessary.
• Alternatives to NULL include using default values (like 0 for numbers, or empty strings for text), but this isn't always appropriate when handling incomplete data.
• In SQL queries, NULL values need special handling. You use IS NULL or IS NOT NULL in the WHERE clause to filter for NULLs.

The lesson provides two tables:

• Buildings table (with building names and capacity)
• Employees table (with roles, names, assigned buildings, and years employed)

The exercise at the end asks you to:

• Find the name and role of all employees who have not been assigned to a building.
• Find the names of the buildings that hold no employees.

This helps reinforce using SQL to query for NULLs or missing assignments in relational data.[1]

1

Here are several alternative ways to write your SQL query that finds the names of buildings with no employees assigned to them:

1. Using RIGHT JOIN instead of LEFT JOIN ✅
You can reverse the order of tables and use a RIGHT JOIN, checking for NULL in the Employee table:

SELECT B.Building_name
FROM Employees E
RIGHT JOIN Buildings B ON B.Building_name = E.Building
WHERE E.Role IS NULL;

2. Using NOT EXISTS with a subquery ✅
This approach selects buildings with no matching employees:

SELECT Building_name
FROM Buildings B
WHERE NOT EXISTS (
    SELECT 1
    FROM Employees E
    WHERE E.Building = B.Building_name
);

3. Using NOT IN with a subquery ✅
This method checks for buildings not present in the Employees table:

SELECT Building_name
FROM Buildings
WHERE Building_name NOT IN (
    SELECT DISTINCT Building
    FROM Employees
    WHERE Building IS NOT NULL
);

All three queries will return the same result: buildings that have no employees assigned. You can choose the style that best fits your context or preference.

1