Skip to content

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 employees who share a manager, or matching students with similar scores. 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.

Required step: Make sure you understand JOIN from Stage 6 and IS NULL from Stage 5. If you do not have these tables yet, run this setup in the playground:

CREATE DATABASE IF NOT EXISTS school;
USE school;

CREATE TABLE IF NOT EXISTS employees (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  manager_id INT,
  department_id INT,
  FOREIGN KEY (manager_id) REFERENCES employees(id)
);

INSERT INTO employees (name, manager_id, department_id) VALUES
  ('Alice', NULL, 1),
  ('Bob', 1, 1),
  ('Charlie', 1, 2),
  ('Diana', 2, 1),
  ('Eve', 2, 2),
  ('Frank', 3, 2);

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

INSERT INTO enrollments (student_id, course_name, score) VALUES
  (1, 'Math', 90), (1, 'Science', 85),
  (2, 'Math', 78), (3, 'Science', 92);

CREATE TABLE IF NOT EXISTS results (
  id INT AUTO_INCREMENT PRIMARY KEY,
  student_id INT NOT NULL,
  score INT,
  total INT
);

INSERT INTO results (student_id, score, total) VALUES
  (1, 85, 100), (2, 0, 50), (3, 45, 0);

Troubleshooting: If you get "table doesn't exist" errors, re-run the setup above. It uses IF NOT EXISTS so it is safe to run multiple times.

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 employee's manager

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

Joins employees to themselves: 'e' is the employee, 'm' is the manager. LEFT JOIN ensures Alice (no manager) still appears with NULL for manager.

Find employees who share a manager

SELECT e1.name AS employee1, e2.name AS employee2, e1.manager_id
FROM employees e1
INNER JOIN employees e2 ON e1.manager_id = e2.manager_id
  AND e1.id < e2.id;

Finds pairs of employees with the same manager. The e1.id < e2.id condition avoids duplicates (Bob/Charlie but not Charlie/Bob) 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 employees;

Returns 0 instead of NULL for Alice (who has no manager). COALESCE evaluates arguments left to right and returns the first non-NULL.

Multiple fallbacks

SELECT name,
  COALESCE(manager_id, department_id, 0) AS reference_id
FROM employees;

If manager_id is NULL, try department_id. If that is also 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(manager_id, 'No manager') AS manager
FROM employees;

Returns 'No manager' instead of NULL for Alice. IFNULL(expr, fallback) returns fallback when expr is NULL. Equivalent to COALESCE(manager_id, 'No manager').

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

Related Tutorials