Self Joins & NULL Handling
Join a table to itself and handle NULL values with COALESCE, IFNULL, and NULLIF.
Sometimes you need to compare rows within the same table, finding teachers who share a department, or matching teachers with similar salaries. A self join lets you treat one table as two. You also frequently need to replace NULL values in results with meaningful defaults, which is where COALESCE, IFNULL, and NULLIF come in.
Definition
A self join joins a table to itself. COALESCE returns the first non-NULL value from a list of arguments. NULLIF returns NULL if two values are equal, otherwise returns the first value.
Start fresh: Click Reset in the sidebar to clear your database, then run the setup below.
CREATE DATABASE IF NOT EXISTS school;
USE school;
CREATE TABLE IF NOT EXISTS students (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100)
);
CREATE TABLE IF NOT EXISTS teachers (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
department VARCHAR(50) NOT NULL,
hire_date DATE NOT NULL,
manager_id INT,
FOREIGN KEY (manager_id) REFERENCES teachers(id)
);
CREATE TABLE IF NOT EXISTS enrollments (
id INT AUTO_INCREMENT PRIMARY KEY,
student_id INT NOT NULL,
course_name VARCHAR(100) NOT NULL,
score DECIMAL(5,2),
FOREIGN KEY (student_id) REFERENCES students(id)
);
CREATE TABLE IF NOT EXISTS results (
id INT AUTO_INCREMENT PRIMARY KEY,
student_id INT NOT NULL,
score INT,
total INT
);
INSERT INTO students (name, email) VALUES
('Alice', 'alice@school.edu'),
('Bob', 'bob@school.edu'),
('Charlie', 'charlie@school.edu');
INSERT INTO teachers (name, department, hire_date, manager_id) VALUES
('Dr. Reynolds', 'Administration', '2010-08-15', NULL),
('Dr. Carter', 'Mathematics', '2012-09-01', 1),
('Ms. Davis', 'Science', '2014-01-10', 1),
('Mr. Evans', 'Mathematics', '2018-08-20', 2),
('Ms. Foster', 'Mathematics', '2020-01-15', 2),
('Mr. Garcia', 'Science', '2019-09-01', 3);
INSERT INTO enrollments (student_id, course_name, score) VALUES
(1, 'Math', 90), (1, 'Science', 85),
(2, 'Math', 78), (3, 'Science', 92);
INSERT INTO results (student_id, score, total) VALUES
(1, 85, 100), (2, 0, 50), (3, 45, 0);Self Joins
A self join joins a table to itself using different aliases. Each alias represents a different "role" of the same table.
Find each teacher's supervisor
SELECT t.name AS teacher, s.name AS supervisor
FROM teachers t
LEFT JOIN teachers s ON t.manager_id = s.id;Joins teachers to themselves: 't' is the teacher, 's' is the supervisor. LEFT JOIN ensures Dr. Reynolds (no supervisor) still appears with NULL for supervisor.
Find teachers in the same department
SELECT t1.name AS teacher1, t2.name AS teacher2, t1.department
FROM teachers t1
INNER JOIN teachers t2 ON t1.department = t2.department
AND t1.id < t2.id;Finds pairs of teachers in the same department. The t1.id < t2.id condition avoids duplicates (Carter/Evans but not Evans/Carter) and self-pairs.
COALESCE
COALESCE returns the first non-NULL value from its arguments. It works with any number of arguments and is standard SQL.
Replace NULL with a default
SELECT name,
COALESCE(manager_id, 0) AS manager_id
FROM teachers;Returns 0 instead of NULL for Dr. Reynolds (who has no supervisor). COALESCE evaluates arguments left to right and returns the first non-NULL.
Multiple fallbacks
SELECT name,
COALESCE(manager_id, 0) AS ref_id
FROM teachers;If manager_id is NULL, return 0. COALESCE handles as many fallbacks as you need.
IFNULL
IFNULL is MySQL-specific shorthand for COALESCE with exactly two arguments. It is shorter but less portable.
IFNULL
SELECT name,
IFNULL(CAST(manager_id AS CHAR), 'No supervisor') AS supervisor
FROM teachers;Returns 'No supervisor' instead of NULL for Dr. Reynolds. IFNULL(expr, fallback) returns fallback when expr is NULL. Because manager_id is INT, we CAST it to CHAR so the string fallback works correctly. Equivalent to COALESCE(CAST(manager_id AS CHAR), 'No supervisor').
NULLIF
NULLIF returns NULL if both arguments are equal. Otherwise it returns the first argument. It is essential for avoiding divide-by-zero errors.
Avoid divide-by-zero
SELECT student_id, score,
score / NULLIF(total, 0) AS percentage
FROM results;If total is 0, NULLIF(total, 0) returns NULL, and the division returns NULL instead of an error. Without NULLIF, dividing by zero crashes the query.
COUNT(DISTINCT)
COUNT(DISTINCT col) counts only unique non-NULL values. It answers questions like "how many unique students are enrolled?"
Count unique values
SELECT COUNT(DISTINCT student_id) AS unique_students
FROM enrollments;Counts the number of distinct students enrolled in at least one course. Students enrolled in multiple courses are counted once.
Count unique combinations
SELECT COUNT(DISTINCT student_id, course_name) AS unique_enrollments
FROM enrollments;Counts unique (student_id, course_name) pairs. Each student-course combination is counted once.
When to use each:COALESCE vs IFNULL
COALESCE: Standard SQL, works with any database, supports multiple fallbacks. Prefer this for portability.
IFNULL: MySQL-specific, exactly 2 arguments, shorter syntax. Fine for MySQL-only code.
What is a self join?
What does COALESCE(NULL, 'hello', 'world') return?
Why is NULLIF useful for division?
Key Takeaways
- A self join joins a table to itself using different aliases for each "role."
- COALESCE returns the first non-NULL value. It is standard SQL and supports multiple fallbacks.
- IFNULL is MySQL-specific shorthand for COALESCE with 2 arguments.
- NULLIF returns NULL when arguments are equal, essential for avoiding divide-by-zero.
- COUNT(DISTINCT col) counts unique non-NULL values.
Ready to test your knowledge?
Take a Quiz