Introduction to Modern Databases/

...

/

Joining Tables

Joining Tables

Learn how to combine data across tables using INNER, LEFT, RIGHT, FULL, and SELF JOINs in SQL.

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.

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 ...