Lesson 06 ~30 min read

Introduction to SQL Joins

Real databases spread data across multiple tables. Joins are how you bring that data back together. Mastering INNER JOIN and LEFT JOIN gives you the ability to answer almost any question a relational database can answer.

Why Joins Exist

In a well-designed relational database, data is split across multiple tables. Student names are in the students table. Course names are in the courses table. Who is enrolled in what is in the enrolments table. This separation eliminates redundancy — but it means a single useful piece of information ("Panji is enrolled in Web Development") requires combining data from more than one table.

A JOIN is the SQL operation that combines rows from two or more tables based on a related column — typically a foreign key relationship.

our sample tables

students table
┌────────────┬──────────────┬─────┐
│ student_id │ full_name    │ age │
├────────────┼──────────────┼─────┤
│ 1          │ Peter        │ 21  │
│ 2          │ Chisomo      │ 23  │
│ 3          │ Mphatso      │ 20  │
└────────────┴──────────────┴─────┘

enrolments table
┌──────────────┬────────────┬──────────────────┐
│ enrolment_id │ student_id │ course           │
├──────────────┼────────────┼──────────────────┤
│ 101          │ 1          │ Web Development  │
│ 102          │ 1          │ Databases        │
│ 103          │ 2          │ Networks         │
└──────────────┴────────────┴──────────────────┘
-- Note: Mphatso (id 3) has no enrolments

INNER JOIN

An INNER JOIN returns only the rows where there is a match in both tables. Rows from either table that have no matching row in the other are excluded entirely.

sql — INNER JOIN syntax

SELECT columns
FROM table_a
INNER JOIN table_b
  ON table_a.shared_column = table_b.shared_column;

sql — real example

-- Which students are enrolled in which courses?
SELECT
  s.full_name,
  e.course,
  e.enrolment_id
FROM students s
INNER JOIN enrolments e
  ON s.student_id = e.student_id;

-- Result:
-- +---------+-----------------+--------------+
-- | Peter   | Web Development | 101          |
-- | Chisomo   | Databases       | 102          |
-- | Chisomo | Networks        | 103          |
-- +---------+-----------------+--------------+
-- Mphatso does NOT appear — no matching enrolment row

// Table aliases

In the example above, students s gives the table the alias s, and enrolments e gives the alias e. This makes column references shorter and prevents ambiguity when both tables have columns with the same name. Always alias tables in JOIN queries.

Introduction to LEFT JOIN

A LEFT JOIN returns all rows from the left table, plus any matching rows from the right table. If there is no match in the right table, the right table's columns appear as NULL.

This is the key difference from INNER JOIN: LEFT JOIN never drops rows from the left table, even if they have no match.

sql — LEFT JOIN

-- All students, with their enrolments (or NULL if none)
SELECT
  s.full_name,
  e.course
FROM students s
LEFT JOIN enrolments e
  ON s.student_id = e.student_id;

-- Result:
-- +---------+-----------------+
-- | Peter   | Web Development |
-- | Peter   | Databases       |
-- | Chisomo | Networks        |
-- | Mphatso | NULL            |  ← appears with NULL course
-- +---------+-----------------+

INNER JOIN vs LEFT JOIN — When to Use Which

INNER JOIN

Use when you only want records that have a match on both sides. "Show me students who are enrolled in at least one course." Non-matching rows are silently excluded.

LEFT JOIN

Use when you want all records from the left table regardless of whether a match exists. "Show me all students, and any courses they are enrolled in." Non-matching rows appear with NULL values.

sql — practical pattern: find unmatched rows

-- Students who are NOT enrolled in any course
-- LEFT JOIN + WHERE NULL is a classic pattern
SELECT s.full_name
FROM students s
LEFT JOIN enrolments e
  ON s.student_id = e.student_id
WHERE e.student_id IS NULL;

-- Result: Mphatso (the only student with no enrolments)

// Knowledge Check

You run an INNER JOIN between students and enrolments. Student "Dalitso" exists in the students table but has no rows in enrolments. Does Dalitso appear in the result?