Search⌘ K
AI Features
Home
Courses
Introduction to Modern Databases

Joining Tables

Explore the purpose and techniques of joining tables in SQL to combine data from multiple sources. Understand different join types such as INNER, LEFT, RIGHT, FULL OUTER, and SELF JOIN. Learn how to write queries that reveal complex relationships and produce comprehensive datasets by combining information from several tables.

We'll cover the following...

So far, we’ve been working with data from single tables.

But what if we need to answer a question like: What are the names of the customers who bought a ‘Laptop’? The customer names are in the Customers table, and the product information is in the Orders table. How do we connect them?

That’s where the magic of JOINs comes in. They are the backbone of relational databases, enabling us to combine data from multiple tables into a single, powerful query.

SQL JOINs allow us to combine rows from two or more tables based on a related column. Different types of joins, such as INNER, LEFT, RIGHT, and FULL JOIN, help us control how data is combined depending on our needs.

By the end of this lesson, we’ll be able to:

  • Understand the fundamental purpose of joining tables in a relational database.

  • Write queries using INNER JOIN to combine rows that match across tables.

  • Use LEFT JOIN and RIGHT JOIN to include data that doesn’t have a match.

  • See how a FULL OUTER JOIN can show us the complete picture from two tables.

  • Master the SELF JOIN technique to query relationships within a single table.

Let’s get started!

What is a join, and why do we need it?

In a well-designed relational database, data is split into multiple tables to avoid redundancy.

For example, instead of storing the full category name like ‘Electronics’ in every single row of the Products table, we store a CategoryID. This makes the database more efficient and easier to maintain. But when we need to generate a report, we don’t want to show the user a CategoryID; we want to show them the actual category name.

This is the core reason for JOINs: they let us combine rows from two or more tables based on a related column between them. Think of it as putting puzzle pieces together. Each table is a piece, and the related columns (like CategoryID in Products and Categories) are the matching edges that allow us to connect them and see the full picture.

Common SQL join types

The common SQL join types are as follows:

  • INNER JOIN: Returns only matching rows

  • LEFT JOIN: Returns all rows from the left table

  • RIGHT JOIN: Returns all rows from the right table

  • FULL OUTER JOIN: Returns all rows from both tables

  • SELF JOIN: Joins a table with itself

The INNER JOIN

The most common type of join is the INNER JOIN. It retrieves records that have matching values in both tables. If a customer in the Customers table has never placed an order, that customer won’t appear in the results of the INNER JOIN on the Customers and Orders table. It will only show the data that overlaps.

Let’s demonstrate this in SQL.

MySQL 8.0
SELECT 
    c.CustomerID,
    c.CustomerName,
    o.OrderID,
    o.OrderDate
FROM Customers AS c
INNER JOIN Orders AS o
    ON c.CustomerID = o.CustomerID;

In this query:

  1. FROM Customers AS c tells the database we’re starting with the Customers table and giving it a short alias, c, to make the query easier to read.

  2. INNER JOIN Orders AS o specifies that we want to join it with the Orders table, which we’ve aliased as o.

  3. ON c.CustomerID = o.CustomerID; is the crucial part. This is the join condition. It tells the database to match rows where the CustomerID in the Customers table is the same as the CustomerID in the Orders table.

The result is a combined dataset that merges information from both tables. This produces a clear, human-readable list of customers and their corresponding orders—a practical way to view related data from multiple sources in one unified result set.

OUTER JOINs (LEFT, RIGHT, and FULL)

What happens when we want to see all the data from one table, even if there aren’t any matching records in the other? An INNER JOIN would exclude this data. This is where OUTER JOINs are essential.

LEFT JOIN

A LEFT JOIN (or LEFT OUTER JOIN) returns all records from the left table (the first one mentioned) and the matched records from the right table. If there’s no match for a record from the left table, the result for the right table's columns will be NULL.

This approach is especially useful when you want to find items without a corresponding match.

For example, suppose we want to list all customers along with any orders they’ve placed. We need to include every customer in the results—even those who haven’t made a purchase yet.

MySQL 8.0
-- Select all customers and their order dates, including customers with no orders
SELECT
    c.CustomerName,
    o.OrderDate
FROM
    Customers AS c
LEFT JOIN
    Orders AS o ON c.CustomerID = o.CustomerID
ORDER BY
    o.OrderDate DESC;

Here, Customers is our LEFT table. The query will return every customer. If a customer has placed orders, the OrderDate will be shown. If a customer has never placed an order, their name will still appear, but the OrderDate column will be NULL.

RIGHT JOIN

A RIGHT JOIN (or RIGHT OUTER JOIN) is the mirror image of a LEFT JOIN.

It returns all records from the right table (the second one mentioned) and the matched records from the left table. If there’s no match, the columns from the left table will be NULL. In practice, RIGHT JOIN is used less frequently because you can almost always get the same result by swapping the tables and using a LEFT JOIN, which many people find more intuitive.

For the sake of completeness, here’s how we would list all orders and their corresponding customer names, ensuring every order is shown.

MySQL 8.0
-- Select all orders and the names of the customers who placed them
SELECT
    c.CustomerName,
    o.OrderDate
FROM
    Orders AS o
RIGHT JOIN
    Customers AS c ON o.CustomerID = c.CustomerID;

This query gives a similar result to our previous LEFT JOIN but starts from the perspective of the Orders table.

FULL OUTER JOIN

A FULL OUTER JOIN combines the results of both LEFT and RIGHT joins. It returns all records when there is a match in either the left or the right table. It’s used when we want to view all the data from both tables and identify where the overlaps occur (and where they don’t).

Important Note: MySQL does not support the FULL OUTER JOIN keyword directly. However, we can simulate it by combining a LEFT JOIN and a RIGHT JOIN with the UNION operator.

MySQL 8.0
-- Simulate a FULL OUTER JOIN to show all customers and all orders
-- (This syntax works in MySQL)
SELECT c.CustomerName, o.OrderDate
FROM Customers AS c
LEFT JOIN Orders AS o ON c.CustomerID = o.CustomerID
UNION
SELECT c.CustomerName, o.OrderDate
FROM Customers AS c
RIGHT JOIN Orders AS o ON c.CustomerID = o.CustomerID;

This query ensures that every customer and every order appears in the result set at least once.

A CROSS JOIN returns all possible combinations of rows from two tables and is also known as a Cartesian product.

The SELF JOIN

Sometimes, a table contains relationships within its own data. For example, our Customers table has a ReferralID column, which contains the CustomerID of the person who referred them. How can we find the names of both the referrer and the referred customer in a single line?

We can’t just query the Customers table once.

The solution is a SELF JOIN, where we join a table to itself. To do this, we must use table aliases to give the table two different temporary names, allowing the database to treat it as two separate tables. Let’s find out which customers referred new customers.

MySQL 8.0
-- Find the names of referring customers and the customers they referred
SELECT
    referrer.CustomerName AS ReferrerName,
    referred.CustomerName AS ReferredCustomerName
FROM
    Customers AS referrer
INNER JOIN
    Customers AS referred ON referrer.CustomerID = referred.ReferralID;

In this query:

  1. We treat the Customers table as two different tables: referrer and referred.

  2. The JOIN condition ON referrer.CustomerID = referred.ReferralID links the two. It matches the CustomerID of the referrer with the ReferralID of the person they referred.

  3. This effectively connects a row in the table to another row in the same table, revealing the hierarchical relationship.

Joining multiple tables

The real power of JOINs shines when we need to connect more than two tables. We can chain JOIN clauses together to navigate complex relationships.

Let's tackle our original question: List the customer name, the product they bought, and the quantity. This requires data from four different tables: Customers, Orders, Order_Details, and Products.

We can build this query step-by-step:

  1. Connect Customers to Orders on CustomerID.

  2. Connect the result to Order_Details on OrderID.

Connect that result to Products on ProductID.

MySQL 8.0
-- Get customer names, the products they ordered, and the quantity of each product
SELECT
    c.CustomerName,
    p.ProductName,
    od.Quantity
FROM
    Customers AS c
JOIN
    Orders AS o ON c.CustomerID = o.CustomerID
JOIN
    Order_Details AS od ON o.OrderID = od.OrderID
JOIN
    Products AS p ON od.ProductID = p.ProductID
ORDER BY
    c.CustomerName;

This single query effectively combines related information from four tables to produce a clear and comprehensive result. Mastering multi-table joins is a key skill for any database professional.

Quiz

Time to test your knowledge! Choose the best answer for each question.

1.

Which JOIN returns only the rows where the join condition is met in both tables?

A.

LEFT JOIN

B.

RIGHT JOIN

C.

INNER JOIN

D.

FULL OUTER JOIN

1 / 7

Excellent work making it through this lesson!

We’ve just unlocked one of the most powerful and essential features of SQL. We've seen how JOINs are the glue that holds a relational database together, allowing us to combine data from multiple tables to get meaningful answers.

We covered the fundamental INNER JOIN for matching data, the various OUTER JOINs for including non-matching data, and the clever SELF JOIN for querying relationships within a single table.

Mastering JOINs is a huge milestone. It transforms us from someone who can just look at data to someone who can ask complex questions and get insightful answers. Keep practicing and keep up the fantastic momentum!