SQL JOINs: Combining Data from Multiple Tables

In a relational database, data is organized into multiple tables to reduce redundancy and improve data integrity. This is called normalization. For example, instead of storing a department's name in every single employee's record, we store a department_id in the employees table and have a separate departments table that maps that ID to the department's name, location, and other details.

This is efficient for storage, but when we need to generate a report, we want to see the employee's name and their department's name together. A JOIN clause is the SQL mechanism for combining rows from two or more tables based on a related column between them. It's the fundamental operation for retrieving meaningful, connected data from a normalized database.

Throughout this lesson, we'll use two simple tables to illustrate how different joins work. Imagine we run a small company.

First, our employees table. Notice that David does not have a department_id.

Second, our departments table. Notice that the 'Sales' department exists, but no employees are assigned to it yet.

The employees.department_id column is a foreign key that references the departments.id column. This relationship is what we'll use in our ON clause to connect them.

INNER JOIN is the most common join type. It selects all rows from both participating tables as long as there is a match between the columns in the ON clause. If a row in one table does not have a corresponding match in the other, it is excluded from the result. Think of it as the intersection in a Venn diagram.

Let's write a query to get a list of employees and the name of the department they work in. We'll join employees and departments on their related ID columns.

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

This query produces the following result:

Notice two things: David is missing because his department_id is NULL and therefore has no match in the departments table. The 'Sales' department is missing because no employee has department_id = 3, so it has no match in the employees table.

A LEFT JOIN (or LEFT OUTER JOIN) returns all rows from the left table (employees in our example), and the matched rows from the right table (departments). If there is no match for a row from the left table, the columns from the right table will contain NULL values. This is useful when you want to see all the data from one primary table, regardless of whether it has a match in the other.

Let's run the same query, but change INNER to LEFT. The "left" table is the one specified first, right after FROM.

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

Now the result includes every employee:

As you can see, David is now in our result set. Since his department_id was NULL, there was no matching id in the departments table, so department_name is NULL for his row. The 'Sales' department is still excluded because it's in the right table and had no matches.

A RIGHT JOIN (or RIGHT OUTER JOIN) is the exact opposite of a LEFT JOIN. It returns all rows from the right table (departments), and the matched rows from the left table (employees). If there's no match, the columns from the left table will be NULL.

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

This gives us a result that includes every department:

Now the 'Sales' department appears, but since no employee is assigned to it, employee_name is NULL. David is gone because he is in the left table and had no match. In practice, RIGHT JOIN is less common; developers often prefer to reorder their tables and consistently use LEFT JOIN.

A FULL OUTER JOIN combines the results of both LEFT and RIGHT joins. It returns all rows from both tables. It will place NULL on either side of the result where a match is not found. This join can produce very large result sets and should be used with care.

SELECT
  employees.name AS employee_name,
  departments.name AS department_name
FROM employees
FULL OUTER JOIN departments ON employees.department_id = departments.id;

This query gives us everyone and every department:

Heads up: Some database systems, like MySQL, do not support FULL OUTER JOIN syntax directly. In those cases, you can emulate it by taking the UNION of a LEFT JOIN and a RIGHT JOIN.

A very common task is to find rows in one table that don't have a match in another. For example, how can we find all employees who haven't been assigned to a department? A LEFT JOIN combined with a WHERE clause is perfect for this.

We start with a LEFT JOIN to keep all employees. We then filter this result, keeping only the rows where the column from the right table is NULL, which indicates no match was found.

SELECT employees.name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.id
WHERE departments.id IS NULL;

This query would correctly return a single result: David. This pattern is extremely useful for data cleaning and integrity checks, such as finding products without categories or customers without orders.

Choosing the right join is crucial for getting the data you need. Here's a quick summary to help you decide.

Most of the time, you'll find yourself reaching for INNER JOIN and LEFT JOIN. They cover the vast majority of day-to-day use cases for combining data.