JOINs: Inner, Left, Right, Full & Self

JOINs — combine tables

JOINs link rows from two or more tables using a related column — usually a primary key and foreign key.

Without JOINs you cannot answer 'student name + course name + score' because those live in different tables.

Real-life example: JOINs are like matching roll numbers on two lists — attendance sheet + marks sheet = full report.

SELECT
  s.full_name,
  c.course_name,
  e.score
FROM enrollments e
INNER JOIN students s ON e.student_id = s.student_id
INNER JOIN courses c ON e.course_id = c.course_id;

INNER JOIN — only matching rows

INNER JOIN returns rows where the join condition matches on both sides. Students with no enrollments do not appear.

Real-life example: INNER JOIN is a strict handshake — both people must show up or there is no meeting.

SELECT DISTINCT s.full_name, s.grade_level
FROM students s
INNER JOIN enrollments e ON s.student_id = e.student_id;

LEFT JOIN — keep all left-table rows

LEFT JOIN keeps every row from the left table. If no match on the right, right columns are NULL.

Real-life example: LEFT JOIN is 'all students, and show their score if they enrolled' — blank if they did not.

SELECT s.full_name, c.course_name, e.score
FROM students s
LEFT JOIN enrollments e ON s.student_id = e.student_id
LEFT JOIN courses c ON e.course_id = c.course_id;

RIGHT JOIN — keep all right-table rows

RIGHT JOIN is the mirror of LEFT JOIN — all rows from the right table, NULLs on the left when missing.

Real-life example: RIGHT JOIN is 'every course, even if zero students signed up' — empty seats still listed.

SELECT c.course_name, s.full_name
FROM students s
RIGHT JOIN enrollments e ON s.student_id = e.student_id
RIGHT JOIN courses c ON e.course_id = c.course_id;

FULL OUTER JOIN — keep both sides

FULL OUTER JOIN returns all rows from both tables. Unmatched sides show NULL. SQL Server supports it; MySQL does not (use UNION of LEFT and RIGHT).

Real-life example: FULL OUTER JOIN is merging two guest lists and keeping everyone, even if they appear on only one list.

SELECT s.full_name, c.course_name
FROM students s
FULL OUTER JOIN enrollments e ON s.student_id = e.student_id
FULL OUTER JOIN courses c ON e.course_id = c.course_id;

SELF JOIN — join a table to itself

SELF JOIN uses two aliases of the same table to compare rows within one table — peers in the same grade, employees and managers, etc.

Real-life example: SELF JOIN is like pairing students in the same class to find study buddies from the same grade_level.

SELECT
  a.full_name AS student_a,
  b.full_name AS student_b,
  a.grade_level
FROM students a
INNER JOIN students b
  ON a.grade_level = b.grade_level
 AND a.student_id < b.student_id;