Working with NULL values when joining data

Imagine you're joining two tables: students and enrollments. If the enrollments table doesn't have info about a student, but you use something like LEFT JOIN, the rows from students will still show up, but the info from enrollments will be missing. In those cases, you get NULL instead of actual data.

Here's what it looks like:

students table:

enrollments table:

Query with LEFT JOIN:

SELECT students.id, students.name, enrollments.course_name
FROM students
LEFT JOIN enrollments ON students.id = enrollments.student_id;

Result:

Well, hello there, NULL! As you can see, for Anna, who isn't enrolled in any course, the course info is missing, so you get NULL instead of anything else.

How does NULL affect queries?

NULL isn't "zero" or "empty string", it's the absence of a value. This behavior has a few interesting (and sometimes annoying) consequences:

Comparisons with NULL:

If you write something like WHERE course_name = NULL, the query won't return rows with NULL. Why? Because you can't compare values directly to NULL.

To check for NULL, you gotta use special operators:

WHERE course_name IS NULL

Math operations:

Any operation with NULL gives you NULL back. For example:

SELECT 5 + NULL; -- result: NULL

Aggregate functions:

Most aggregate functions like SUM(), AVG() ignore NULL, but COUNT(*) counts them as "existing rows".

How do you deal with NULL?

1. Replace NULL with something readable using COALESCE()

The COALESCE() function lets you swap out NULL for another value. For example, if a course is missing, you can show "No course":

SELECT
    students.id, 
    students.name, 
    COALESCE(enrollments.course_name, 'No course') AS course_name
FROM 
    students LEFT JOIN enrollments 
    ON students.id = enrollments.student_id;

Result:

Looks way better now, right?

2. Filtering out NULL values

If you don't wanna see rows with NULL, you can use WHERE ... IS NOT NULL. For example:

SELECT
    students.id, 
    students.name, 
    enrollments.course_name
FROM 
    students LEFT JOIN enrollments 
    ON students.id = enrollments.student_id
WHERE 
    enrollments.course_name IS NOT NULL;

Result:

Anna disappears from the result, since she doesn't have any course enrollments.

3. Counting with NULL: example with COUNT

Like we said earlier, some functions ignore NULL, some don't. For example:

To count all rows, including those where NULL is present:

SELECT COUNT(*) FROM students; -- Counts ALL rows (including those where `course_name` = NULL)

To count only rows where there's no NULL:

SELECT COUNT(course_name) FROM enrollments;

4. Conditional expressions with CASE

If you don't like COALESCE() or want more flexibility, try using CASE. For example:

SELECT
    students.id, 
    students.name,
    CASE
        WHEN enrollments.course_name IS NULL THEN 'No course'
        ELSE enrollments.course_name
    END AS course_name
FROM 
    students LEFT JOIN enrollments 
    ON students.id = enrollments.student_id;

The result will be the same as with COALESCE(), but CASE lets you write more complex rules.

5. Use INNER JOIN if you're sure there won't be any NULL

The most radical way to avoid NULL is to not let them show up at all, by using INNER JOIN. This type of join only returns rows with matches in both tables:

SELECT
    students.id, 
    students.name, 
    enrollments.course_name
FROM 
    students INNER JOIN enrollments 
    ON students.id = enrollments.student_id;

No surprises—only students who are enrolled in courses.

Result:

If your data needs to show all values, including NULL, INNER JOIN won't work, but sometimes that's all you need.