Skip to content

Joins

Combine data from multiple tables using INNER JOIN and LEFT JOIN.

Real data is spread across multiple tables. A student has enrollments. An enrollment references a course. To answer questions like "which students are enrolled in which courses," you need to combine data from two or more tables. That is what joins do.

Definition

A SQL JOIN combines rows from two or more tables based on a related column. An INNER JOIN returns only matching rows. A LEFT JOIN returns all rows from the left table, with matching rows from the right table (or NULL if no match).

Required step: If you do not have these tables yet, run this setup in the playground before continuing. These tables are used by every example in this stage and later stages. The FOREIGN KEY line links enrollments to students; it is formally explained in Stage 8.

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

INSERT INTO students (name, email) VALUES
  ('Alice', '[email protected]'),
  ('Bob', '[email protected]'),
  ('Charlie', '[email protected]');

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

INNER JOIN

INNER JOIN returns only rows that have matching values in both tables. Rows without a match are discarded.

Students with their enrollments

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

The ON clause defines the relationship: students.id matches enrollments.student_id. Only students with enrollments appear. Charlie has no enrollments, so he is not returned.

Table aliases

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

INNER JOIN can be shortened to just JOIN. The alias 's' means you can write s.name instead of students.name. Both FROM students s and FROM students AS s are valid.

LEFT JOIN

LEFT JOIN returns all rows from the left table, with NULLs where no match exists in the right table.

All students, even without enrollments

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

Alice and Bob have enrollments (course_name shows Math, Science). Charlie has no enrollments, so his course_name is NULL. All three students appear.

Find students with no enrollments

SELECT s.name
FROM students s
LEFT JOIN enrollments e ON s.id = e.student_id
WHERE e.id IS NULL;

LEFT JOIN keeps all students. WHERE e.id IS NULL filters to only those with no matching enrollment. This is the standard pattern for finding unmatched rows.

Key difference:INNER JOIN vs LEFT JOIN

  • INNER JOIN: only rows with matches in both tables
  • LEFT JOIN: all rows from the left table, NULLs where no match
  • If a student has no enrollments, INNER JOIN omits them; LEFT JOIN includes them with NULL

What does INNER JOIN return?

A student has no enrollments. What appears in the course_name column with LEFT JOIN?

Which is a valid way to alias a table?

Key Takeaways

  • INNER JOIN returns only rows with matches in both tables.
  • LEFT JOIN returns all rows from the left table, with NULLs where no match exists.
  • Always specify join conditions with ON to avoid Cartesian products.
  • Use table aliases for cleaner, shorter queries.
  • LEFT JOIN + WHERE ... IS NULL is the standard way to find unmatched rows.

Ready to test your knowledge?

Take a Quiz

Related Tutorials