QUERYINGINTERMEDIATElesson-08

JOINs — Combining Multiple Tables

🕑 38 min

📃 5 sections

🎓 Querying

Relational databases split data into multiple tables to avoid redundancy — students in one table, courses in another, enrollments in a third. JOINs bring that data back together at query time. Mastering INNER JOIN, LEFT JOIN, and when to use each is the single biggest skill jump in your SQL journey.

Section 1

Why JOINs Exist

Without JOINs, you'd repeat every student's name and age in every enrollment row. That's data redundancy — it wastes space and creates inconsistency when data changes.

The solution: normalize into separate tables connected by FOREIGN KEYs, then use JOINs to reassemble the data at query time.

SQL

-- Three related tables
CREATE TABLE courses (
  id          INT PRIMARY KEY AUTO_INCREMENT,
  course_name VARCHAR(100) NOT NULL,
  teacher     VARCHAR(100)
);

CREATE TABLE enrollments (
  id           INT PRIMARY KEY AUTO_INCREMENT,
  student_id   INT NOT NULL,
  course_id    INT NOT NULL,
  enrolled_on  DATE DEFAULT (CURRENT_DATE),
  FOREIGN KEY (student_id) REFERENCES students(id),
  FOREIGN KEY (course_id)  REFERENCES courses(id)
);

Section 2

INNER JOIN

SQL

-- INNER JOIN: only rows that match in BOTH tables
SELECT s.name, c.course_name, e.enrolled_on
FROM students s
INNER JOIN enrollments e ON s.id = e.student_id
INNER JOIN courses c     ON e.course_id = c.id;

-- Shorter: JOIN = INNER JOIN
SELECT s.name, c.course_name
FROM students s
JOIN enrollments e ON s.id = e.student_id
JOIN courses c     ON e.course_id = c.id
WHERE c.course_name = 'Python Basics';

💡

Use Table Aliases

Use table aliases (s, e, c) with JOINs to keep queries readable. Without aliases, column names become very verbose and hard to scan.

Section 3

LEFT JOIN

SQL

-- LEFT JOIN: ALL rows from left table + matching rows from right
-- Non-matching rows in right table get NULL
SELECT s.name, c.course_name
FROM students s
LEFT JOIN enrollments e ON s.id = e.student_id
LEFT JOIN courses c     ON e.course_id = c.id;
-- Students not enrolled in any course show NULL for course_name

-- Find students NOT enrolled in any course
SELECT s.name
FROM students s
LEFT JOIN enrollments e ON s.id = e.student_id
WHERE e.id IS NULL;    -- NULL = no matching enrollment

Section 4

RIGHT JOIN and FULL OUTER

SQL

-- RIGHT JOIN: ALL rows from right table + matching from left
SELECT s.name, c.course_name
FROM students s
RIGHT JOIN enrollments e ON s.id = e.student_id;

-- FULL OUTER JOIN (all rows from both, NULL where no match)
-- MySQL doesn't support FULL OUTER JOIN directly — use UNION:
SELECT s.name, c.course_name
FROM students s LEFT JOIN enrollments e ON s.id = e.student_id
LEFT JOIN courses c ON e.course_id = c.id
UNION
SELECT s.name, c.course_name
FROM students s RIGHT JOIN enrollments e ON s.id = e.student_id
RIGHT JOIN courses c ON e.course_id = c.id;

Section 5

SELF JOIN

SQL

-- Self JOIN: join a table to itself
-- Use case: employee to manager hierarchy
CREATE TABLE employees (
  id         INT PRIMARY KEY,
  name       VARCHAR(100),
  manager_id INT,                        -- references same table
  FOREIGN KEY (manager_id) REFERENCES employees(id)
);

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

👥

INNER JOIN

Only rows with matching values in both tables

Most Common

←

LEFT JOIN

All rows from left table plus matched rows from right (NULL where no match)

Left Priority

→

RIGHT JOIN

All rows from right table plus matched rows from left (NULL where no match)

Right Priority

↔

FULL OUTER

All rows from both tables, NULL where no match exists on either side

UNION trick

🔁

SELF JOIN

Table joined to itself — for hierarchical or recursive data

Hierarchy

⚡

CROSS JOIN

Cartesian product — every row combined with every other row

Rare

🎤 Knowledge Check

1. INNER JOIN returns?

INNER JOIN only returns rows where a match exists in both tables. Students with no enrollments, and courses with no students, are excluded.

2. A LEFT JOIN where right side has no match shows?

LEFT JOIN always keeps every row from the left table. When there's no match on the right, it fills those columns with NULL.

3. How do you find students NOT enrolled in any course using LEFT JOIN?

After a LEFT JOIN, unenrolled students will have NULL in the enrollment table columns. WHERE enrollment_id IS NULL filters to only those students.

4. What is a FOREIGN KEY?

A FOREIGN KEY creates a relational link — it says "this value must exist as a PRIMARY KEY in that other table." This enforces referential integrity.

5. SELF JOIN is used for?

SELF JOINs are used for hierarchical data — like employees and their managers when both exist in the same table. You just alias the table twice.