SQL Joins

In the SQL Basics tutorial, we worked with a single table at a time. But real databases almost always have multiple related tables — customers and orders, students and courses, products and categories. Joins are how you combine rows from two or more tables based on a related column.

Understanding joins is one of the most important SQL skills you can have. They come up constantly in application development, data analysis, and technical interviews.

Setting Up the Example Data

We’ll use a small database with three tables: students, courses, and enrollments. If you followed the SQL Basics tutorial, you’ve already seen these. Let’s start fresh so everything is consistent:

CREATE TABLE students (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE
);

CREATE TABLE courses (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    instructor TEXT
);

CREATE TABLE enrollments (
    student_id INTEGER,
    course_id INTEGER,
    grade TEXT,
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (course_id) REFERENCES courses(id)
);

INSERT INTO students (name, email) VALUES ('Alice', 'alice@example.com');
INSERT INTO students (name, email) VALUES ('Bob', 'bob@example.com');
INSERT INTO students (name, email) VALUES ('Charlie', 'charlie@example.com');
INSERT INTO students (name, email) VALUES ('Diana', 'diana@example.com');

INSERT INTO courses (title, instructor) VALUES ('Intro to SQL', 'Dr. Smith');
INSERT INTO courses (title, instructor) VALUES ('Web Development', 'Prof. Jones');
INSERT INTO courses (title, instructor) VALUES ('Data Science', 'Dr. Lee');

INSERT INTO enrollments (student_id, course_id, grade) VALUES (1, 1, 'A');
INSERT INTO enrollments (student_id, course_id, grade) VALUES (1, 2, 'B');
INSERT INTO enrollments (student_id, course_id, grade) VALUES (2, 1, 'A');
INSERT INTO enrollments (student_id, course_id, grade) VALUES (3, 3, 'C');

Notice that Diana (id 4) isn’t enrolled in any course, and Bob (id 2) is only in one course. These gaps will help illustrate the differences between join types.

INNER JOIN

An INNER JOIN returns only the rows where there’s a match in both tables. If a row in one table has no corresponding row in the other, it’s excluded from the results.

SELECT students.name, courses.title, enrollments.grade
FROM enrollments
INNER JOIN students ON enrollments.student_id = students.id
INNER JOIN courses ON enrollments.course_id = courses.id;

Diana doesn’t appear because she has no enrollment records. The Data Science course shows up because Charlie is enrolled in it. This is the most common type of join — when you only want rows that have matching data on both sides.

LEFT JOIN

A LEFT JOIN returns all rows from the left table, plus matching rows from the right table. If there’s no match, the right side columns are filled with NULL.

SELECT students.name, courses.title, enrollments.grade
FROM students
LEFT JOIN enrollments ON students.id = enrollments.student_id
LEFT JOIN courses ON enrollments.course_id = courses.id;

Now Diana appears — with NULL for the course and grade columns since she’s not enrolled in anything. This is useful when you want to include records even if they don’t have related data. For example, “show me all students and their courses, including students who haven’t enrolled yet.”

RIGHT JOIN

A RIGHT JOIN is the mirror of a LEFT JOIN — it returns all rows from the right table, plus matching rows from the left table.

SELECT students.name, courses.title
FROM enrollments
RIGHT JOIN courses ON enrollments.course_id = courses.id
LEFT JOIN students ON enrollments.student_id = students.id;

In practice, most developers prefer LEFT JOIN and just rearrange the table order rather than using RIGHT JOIN. The result is the same, and it’s easier to read consistently.

FULL OUTER JOIN

A FULL OUTER JOIN returns all rows from both tables. Where there’s a match, you get the combined data. Where there isn’t, you get NULL on the side that’s missing.

-- PostgreSQL / SQL Server syntax
SELECT students.name, courses.title
FROM students
FULL OUTER JOIN enrollments ON students.id = enrollments.student_id
FULL OUTER JOIN courses ON enrollments.course_id = courses.id;

This is the most inclusive join — nothing gets dropped. It’s less common in everyday queries but useful when you need a complete picture of both sides.

CROSS JOIN

A CROSS JOIN produces the Cartesian product of two tables — every row from the first table paired with every row from the second. No ON condition is needed.

SELECT students.name, courses.title
FROM students
CROSS JOIN courses;

This returns 4 students × 3 courses = 12 rows. Every possible student-course combination.

Cross joins are rarely used in application code, but they’re handy for generating test data or creating lookup combinations.

Table Aliases

When queries involve multiple tables, column names can get verbose. Aliases let you give tables short names:

SELECT s.name, c.title, e.grade
FROM enrollments e
JOIN students s ON e.student_id = s.id
JOIN courses c ON e.course_id = c.id;

Same result as our first INNER JOIN query, but much easier to read. You’ll see aliases used in virtually every multi-table query.

Self Joins

A self join is when a table is joined to itself. This is useful for hierarchical or comparative data. Imagine an employees table where each employee has a manager_id that references another employee:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER,
    FOREIGN KEY (manager_id) REFERENCES employees(id)
);

INSERT INTO employees (name, manager_id) VALUES ('Sarah', NULL);
INSERT INTO employees (name, manager_id) VALUES ('Tom', 1);
INSERT INTO employees (name, manager_id) VALUES ('Lisa', 1);
INSERT INTO employees (name, manager_id) VALUES ('Mike', 2);

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

Aliases are essential here — without them, the database wouldn’t know which “copy” of the table you’re referring to.

Joining with Conditions

You can add WHERE clauses to filter joined results, just like with single-table queries:

SELECT s.name, c.title, e.grade
FROM enrollments e
JOIN students s ON e.student_id = s.id
JOIN courses c ON e.course_id = c.id
WHERE e.grade = 'A';

You can also use aggregate functions with joins. For example, counting how many courses each student is enrolled in:

SELECT s.name, COUNT(e.course_id) AS course_count
FROM students s
LEFT JOIN enrollments e ON s.id = e.student_id
GROUP BY s.id, s.name
ORDER BY course_count DESC;

We use LEFT JOIN here so Diana (with zero enrollments) still appears in the results.

Quick Reference

What’s Next

Joins are the foundation for working with relational data. Once you’re comfortable with them, you’ll want to explore aggregate functions like COUNT, SUM, and AVG with GROUP BY, and eventually subqueries and window functions for more advanced analysis.