SQL Joins

Every query so far has asked one table, employees, for data, and employees.department has been a plain text column since Module 1. That's been fine for learning WHERE and aggregates, but it hides a real problem: nothing stops two rows from storing 'Engineering' and 'Enginering', and renaming a department means updating every single employee row that mentions it. Real schemas fix this by storing each department once, in its own table, and referencing it by ID. This lesson introduces joins, the tool that puts that split data back together, and formally updates Beacon Robotics' schema to the normalized version used for the rest of this course.

Learning Objectives

• Explain why splitting data into multiple related tables (normalization) avoids duplication and inconsistency.
• Read Beacon Robotics' updated schema: employees, departments, projects, and project_assignments.
• Write a basic JOIN with an ON condition and understand what it actually does.

Why One Table Becomes Several

Storing department as free text on every employee row means the same department name gets typed out over and over, Engineering on one row, Engineering on another, a hundred more times for a hundred employees. Nothing enforces that every row spells it identically, and changing a department's name later means finding and updating every row that mentions it. The fix: store each department exactly once, in its own departments table, and have employees point to it with an ID. This is called normalization, and this course treats it properly starting here, a full module on database design comes later, this lesson only covers the slice needed to understand joins.

Beacon Robotics' Updated Schema

department_id is an integer that points at a row in departments, the same idea as salary or hire_date, just a column whose value happens to be another table's primary key. manager_id works the same way but points back into employees itself, an employee's manager is just another employee, used in the self-join lesson later in this module. project_assignments exists because one employee can work on several projects and one project has several employees, a many-to-many relationship needs its own linking table, covered properly in the Database Design module later in this course. From this lesson forward, every example uses department_id and departments, not the old department text column.

id (primary key)
name

1  Engineering
2  Research
3  Marketing
4  Sales
5  Legal   -- no employees yet

id (primary key)
first_name
last_name
department_id (foreign key -> departments.id)
salary
hire_date
manager_id (foreign key -> employees.id, NULL for whoever has no manager)

id (primary key)
name

1  Warehouse Bot v2
2  Night Vision Sensors
3  Fleet Dashboard

employee_id (foreign key -> employees.id)
project_id (foreign key -> projects.id)

A Basic JOIN

JOIN departments adds a second table to the query. ON employees.department_id = departments.id is the join condition, it tells SQL exactly how a row from employees relates to a row from departments: match them when the employee's department_id equals the department's id. Without ON, SQL has no way to know which department row belongs with which employee row.

Plain JOIN here means INNER JOIN, the default and most common type, covered in full in the next lesson. employees.first_name and departments.name use table.column to say exactly which table each column comes from, necessary once a query involves more than one table.

SELECT employees.first_name, departments.name AS department_name
FROM employees
JOIN departments ON employees.department_id = departments.id;

The Join Types This Module Covers

Each gets its own lesson next, with real Beacon Robotics examples, including the department with no employees (Legal) and the self-referencing manager_id column.

Try It Yourself

Write a query that returns every employee's last_name alongside their department's name.

SELECT employees.last_name, departments.name AS department_name
FROM employees
JOIN departments ON employees.department_id = departments.id;

Frequently Asked Questions

Common Beginner Mistakes

Best Practices

• Write INNER JOIN explicitly rather than relying on the JOIN default, it reads more clearly.
• Always include an ON condition, a JOIN without one is a cross join almost by accident.
• Qualify column names with their table (employees.first_name) once a query touches more than one table.

Interview Questions

Summary

Beacon Robotics' schema is now normalized: departments and projects each hold their own data once, employees references a department through department_id and a manager through manager_id, and project_assignments links employees to projects. A JOIN with an ON condition is how a query pulls matching rows from more than one of these tables back together.

What's Next?

INNER JOIN next, the default join type and the one used above, covered in full with more examples including filtering and multi-table joins.