Understanding SQL Joins

Joins allow you to combine data from multiple tables based on related columns. They're essential for working with normalized databases.

Why Joins?

In a well-designed database, data is split across multiple tables to avoid redundancy. For example:

students table has student information
courses table has course information
enrollments table connects students to courses

To see which students are in which courses, you need to JOIN these tables.

Types of Joins

INNER JOIN

Returns only rows that have matching values in both tables.


sql
1SELECT students.first_name, courses.title
2FROM students
3INNER JOIN enrollments ON students.id = enrollments.student_id
4INNER JOIN courses ON enrollments.course_id = courses.id;

Visual: Only the intersection (matching rows) is returned.

LEFT JOIN (LEFT OUTER JOIN)

Returns all rows from the left table, plus matching rows from the right table.


sql
1SELECT customers.name, orders.total
2FROM customers
3LEFT JOIN orders ON customers.id = orders.customer_id;

If a customer has no orders, they'll still appear with NULL for order columns.

RIGHT JOIN (RIGHT OUTER JOIN)

Returns all rows from the right table, plus matching rows from the left table. Less commonly used than LEFT JOIN.


sql
1SELECT customers.name, orders.total
2FROM customers
3RIGHT JOIN orders ON customers.id = orders.customer_id;

FULL OUTER JOIN

Returns all rows when there's a match in either table.


sql
1SELECT a.name, b.name
2FROM table_a a
3FULL OUTER JOIN table_b b ON a.id = b.a_id;

CROSS JOIN

Returns the Cartesian product - every row from the first table paired with every row from the second table.


sql
1SELECT colors.name, sizes.name
2FROM colors
3CROSS JOIN sizes;

Join Syntax

Traditional vs. ANSI Syntax

Traditional (older):


sql
1SELECT s.name, c.title
2FROM students s, courses c, enrollments e
3WHERE s.id = e.student_id
4AND c.id = e.course_id;

ANSI (preferred):


sql
1SELECT s.name, c.title
2FROM students s
3INNER JOIN enrollments e ON s.id = e.student_id
4INNER JOIN courses c ON e.course_id = c.id;

The ANSI syntax is clearer and separates join conditions from filter conditions.

Table Aliases

Use short aliases to make queries readable:


sql
1SELECT s.first_name, s.last_name, c.title
2FROM students s
3JOIN enrollments e ON s.id = e.student_id
4JOIN courses c ON e.course_id = c.id;

Multiple Joins

You can chain multiple joins together:


sql
1SELECT
2    s.first_name,
3    c.title,
4    i.last_name AS instructor
5FROM students s
6JOIN enrollments e ON s.id = e.student_id
7JOIN courses c ON e.course_id = c.id
8JOIN instructors i ON c.instructor_id = i.id;

Self Joins

A table can be joined to itself to compare rows within the same table:


sql
1-- Find employees and their managers
2SELECT
3    e.name AS employee,
4    m.name AS manager
5FROM employees e
6LEFT JOIN employees m ON e.manager_id = m.id;

Common Patterns

Finding Related Data


sql
1-- Customers with their orders
2SELECT c.name, o.order_date, o.total
3FROM customers c
4JOIN orders o ON c.id = o.customer_id;

Finding Unmatched Records


sql
1-- Customers who have never ordered
2SELECT c.name
3FROM customers c
4LEFT JOIN orders o ON c.id = o.customer_id
5WHERE o.id IS NULL;

Counting Related Records


sql
1-- Count of orders per customer
2SELECT c.name, COUNT(o.id) AS order_count
3FROM customers c
4LEFT JOIN orders o ON c.id = o.customer_id
5GROUP BY c.id, c.name;

Summary

INNER JOIN: Only matching rows from both tables
LEFT JOIN: All left table rows + matching right rows
RIGHT JOIN: All right table rows + matching left rows
FULL JOIN: All rows from both tables
CROSS JOIN: All combinations of rows
Use table aliases for readability
Chain multiple joins for complex queries