Working with Multiple Tables - JOINs

Here’s where SQL gets really interesting. So far, you’ve been working with single tables, but real databases don’t work that way. Information is spread across multiple tables that connect to each other. JOINs are how you bring that data back together, and mastering them will transform you from a SQL beginner to someone who truly understands relational databases.

Why Multiple Tables?

Imagine storing employee data in one giant table with columns like: employee_name, department_name, department_budget, manager_name, manager_email, project_name, project_deadline… It would be a nightmare. Data would repeat everywhere, updates would be error-prone, and the table would be massive.

Instead, we split data into logical tables:

employees table: employee info
departments table: department info
projects table: project info

JOINs let us combine these tables when we need information from multiple sources.

Understanding Table Relationships

Before diving into JOIN syntax, you need to understand how tables connect. They use keys:

Primary Key: Unique identifier for each row (like employee_id)
Foreign Key: Column that references the primary key of another table

Example:

 1employees table:
 2| employee_id | first_name | department_id |
 3|-------------|------------|---------------|
 4| 1           | Sarah      | 10            |
 5| 2           | Mike       | 20            |
 6
 7departments table:
 8| department_id | department_name |
 9|---------------|-----------------|
10| 10            | Engineering     |
11| 20            | Sales           |

The department_id in employees is a foreign key that references department_id in departments.

INNER JOIN: Start Here

INNER JOIN is the most common join. It returns rows only when there’s a match in both tables.

1SELECT employees.first_name, departments.department_name
2FROM employees
3INNER JOIN departments ON employees.department_id = departments.department_id;

Result:

| first_name | department_name |
|------------|-----------------|
| Sarah      | Engineering     |
| Mike       | Sales           |

Notice the table.column syntax - when columns exist in multiple tables, you need to specify which table you mean.

Table Aliases: Your New Best Friend

Writing full table names gets tedious. Use aliases:

1SELECT e.first_name, d.department_name
2FROM employees e
3INNER JOIN departments d ON e.department_id = d.department_id;

Much cleaner! e and d are temporary names for the tables within this query.

LEFT JOIN: Keep Everything from the Left

LEFT JOIN returns all rows from the left table, even if there’s no match in the right table.

1SELECT e.first_name, d.department_name
2FROM employees e
3LEFT JOIN departments d ON e.department_id = d.department_id;

If an employee has no department, you’ll see:

| first_name | department_name |
|------------|-----------------|
| Sarah      | Engineering     |
| Mike       | Sales           |
| John       | NULL            |

John appears even though his department doesn’t exist in the departments table.

RIGHT JOIN: Keep Everything from the Right

RIGHT JOIN is the opposite - keeps all rows from the right table:

1SELECT e.first_name, d.department_name
2FROM employees e
3RIGHT JOIN departments d ON e.department_id = d.department_id;

If there’s a department with no employees, it still appears:

| first_name | department_name |
|------------|-----------------|
| Sarah      | Engineering     |
| Mike       | Sales           |
| NULL       | Marketing       |

FULL OUTER JOIN: Keep Everything

FULL OUTER JOIN keeps all rows from both tables (not supported in all databases):

1SELECT e.first_name, d.department_name
2FROM employees e
3FULL OUTER JOIN departments d ON e.department_id = d.department_id;

You get employees without departments AND departments without employees.

When to Use Which JOIN

INNER JOIN - “Show me only records that have matches in both tables”

Most common
Use when you need data that exists in both tables
Example: Active employees and their current departments

LEFT JOIN - “Show me everything from the first table, plus matching info from the second”

Use when the left table is your “main” data
Example: All customers and their recent orders (even customers with no orders)

RIGHT JOIN - “Show me everything from the second table, plus matching info from the first”

Less common (you can usually rewrite as LEFT JOIN)
Example: All products and their sales (even products that never sold)

FULL OUTER JOIN - “Show me everything from both tables”

Rarely used
Example: Complete audit of employees and departments to find orphaned records

Real-World Examples

Employee information with department details:

1SELECT e.first_name, e.last_name, e.salary, d.department_name, d.budget
2FROM employees e
3INNER JOIN departments d ON e.department_id = d.department_id
4WHERE e.salary > 50000;

All customers and their order history:

1SELECT c.customer_name, c.email, o.order_date, o.total_amount
2FROM customers c
3LEFT JOIN orders o ON c.customer_id = o.customer_id
4ORDER BY c.customer_name, o.order_date DESC;

Products that have never been ordered:

1SELECT p.product_name, p.price
2FROM products p
3LEFT JOIN order_items oi ON p.product_id = oi.product_id
4WHERE oi.product_id IS NULL;

Joining Multiple Tables

You can join more than two tables:

1SELECT e.first_name, d.department_name, p.project_name
2FROM employees e
3INNER JOIN departments d ON e.department_id = d.department_id
4INNER JOIN employee_projects ep ON e.employee_id = ep.employee_id
5INNER JOIN projects p ON ep.project_id = p.project_id;

This gets information from four tables: employees, departments, employee_projects (junction table), and projects.

Common JOIN Patterns

Many-to-many relationships (using junction tables):

1-- Students and their classes
2SELECT s.student_name, c.class_name
3FROM students s
4INNER JOIN student_classes sc ON s.student_id = sc.student_id
5INNER JOIN classes c ON sc.class_id = c.class_id;

Self-joins (table joined to itself):

1-- Employees and their managers
2SELECT e.first_name as employee, m.first_name as manager
3FROM employees e
4LEFT JOIN employees m ON e.manager_id = m.employee_id;

Common Mistakes

Forgetting the ON clause:

1-- Wrong (creates a cartesian product - every row matched with every row):
2SELECT * FROM employees, departments;
3
4-- Right:
5SELECT * FROM employees e
6INNER JOIN departments d ON e.department_id = d.department_id;

Ambiguous column names:

1-- Wrong (if both tables have 'id' column):
2SELECT id, name FROM employees e
3INNER JOIN departments d ON e.department_id = d.department_id;
4
5-- Right:
6SELECT e.employee_id, d.department_name FROM employees e
7INNER JOIN departments d ON e.department_id = d.department_id;

Using the wrong JOIN type:

Need all records from your main table? Use LEFT JOIN
Only need records that match in both tables? Use INNER JOIN

Performance Tips

JOIN conditions should usually involve indexed columns (like primary keys and foreign keys)

Filter early with WHERE clauses:

 1-- Good:
 2SELECT e.first_name, d.department_name
 3FROM employees e
 4INNER JOIN departments d ON e.department_id = d.department_id
 5WHERE e.hire_date > '2020-01-01';
 6
 7-- Less efficient:
 8SELECT e.first_name, d.department_name
 9FROM employees e
10INNER JOIN departments d ON e.department_id = d.department_id
11WHERE e.hire_date > '2020-01-01';

Why JOINs Matter

JOINs are what make relational databases “relational.” They let you:

Store data efficiently without duplication
Maintain data integrity through relationships
Answer complex business questions that span multiple entities
Build flexible systems that can evolve over time

Once you master JOINs, you can work with any relational database in the world. The patterns are the same whether you’re analyzing sales data, managing inventory, or building the next social media platform.

Practice Exercises

Join employees with their departments to show names and department names
Find all customers who have never placed an order (LEFT JOIN with NULL check)
Show all orders with customer information and order details
Find employees and their managers using a self-join
Create a report showing products, their categories, and total sales

Master these JOIN patterns, and you’ll be able to extract meaningful insights from any relational database. This is where SQL moves from simple data retrieval to powerful business intelligence.

Aggregation and Grouping Subqueries and Derived Tables