INNER JOIN

INNER JOIN is the most commonly used type of join. It returns only the rows that have matching values in both tables. If a row in one table does not have a corresponding row in the other table, it is simply excluded from the results.

How INNER JOIN Works

Think of INNER JOIN as the intersection of two sets. Only data that exists in both tables makes it into the result.

SELECT
  s.first_name,
  s.last_name,
  c.name AS course_name
FROM students s
INNER JOIN enrollments e ON s.id = e.student_id
INNER JOIN courses c ON e.course_id = c.id;

This returns only students who are enrolled in at least one course. Students without enrollments and courses without students are excluded.

Explicit vs Implicit Syntax

You will see two ways to write INNER JOINs:

-- Explicit JOIN (recommended)
SELECT s.first_name, c.name
FROM students s
INNER JOIN enrollments e ON s.id = e.student_id
INNER JOIN courses c ON e.course_id = c.id;

-- Implicit JOIN (old style)
SELECT s.first_name, c.name
FROM students s, enrollments e, courses c
WHERE s.id = e.student_id
AND e.course_id = c.id;

Both produce the same result. The explicit JOIN syntax is preferred because it is clearer and harder to accidentally create a cross join.

Filtering with INNER JOIN

You can combine JOIN with WHERE to filter the results:

SELECT
  s.first_name,
  s.last_name,
  c.name AS course_name,
  e.grade
FROM students s
INNER JOIN enrollments e ON s.id = e.student_id
INNER JOIN courses c ON e.course_id = c.id
WHERE e.grade = 'A';

This shows only students who received an A grade. The WHERE clause filters after the join is performed.

Self Joins

Sometimes you need to join a table with itself. This is called a self join:

-- Find students in the same course
SELECT
  a.first_name AS student1,
  b.first_name AS student2,
  c.name AS course_name
FROM enrollments a
INNER JOIN enrollments b ON a.course_id = b.course_id AND a.student_id < b.student_id
INNER JOIN courses c ON a.course_id = c.id;

This finds pairs of students who are enrolled in the same course. The a.student_id < b.student_id condition prevents duplicate pairs.

Multiple Conditions

You can join on multiple conditions:

SELECT s.first_name, c.name, e.semester
FROM students s
INNER JOIN enrollments e
  ON s.id = e.student_id
  AND e.semester = 'Fall 2024'
INNER JOIN courses c ON e.course_id = c.id;

This filters during the join itself rather than in a WHERE clause. Both approaches work, but filtering in the ON clause can sometimes be more readable.

Counting with INNER JOIN

Combine INNER JOIN with aggregate functions for powerful analysis:

SELECT
  s.first_name,
  s.last_name,
  COUNT(e.id) AS enrollment_count
FROM students s
INNER JOIN enrollments e ON s.id = e.student_id
GROUP BY s.id, s.first_name, s.last_name
HAVING COUNT(e.id) > 3;

This shows students who are enrolled in more than 3 courses. The HAVING clause filters groups after aggregation.