Working with Multiple Tables: JOINs

In this lesson, you'll learn how to combine data from multiple tables using JOIN operations. This is a fundamental skill in SQL, allowing you to create meaningful reports and extract valuable information from your databases. You'll master INNER JOIN and LEFT JOIN, understanding how they work and when to use them.

Learning Objectives

  • Define the purpose and function of JOIN operations in SQL.
  • Understand the difference between primary keys and foreign keys and their role in joins.
  • Apply INNER JOIN to combine data from two or more tables based on a common column.
  • Apply LEFT JOIN to retrieve all rows from the left table and matching rows from the right table.

Text-to-Speech

Listen to the lesson content

Lesson Content

Introduction to JOINs: Combining Data

Databases often store related information across multiple tables. For example, you might have a table for 'Customers' and another table for 'Orders'. To see which orders belong to which customers, you need to combine data from these tables. This is where JOINs come in. JOINs allow you to query data from multiple tables simultaneously, based on a relationship between them. This relationship is typically established using primary and foreign keys. A primary key uniquely identifies a row in a table (e.g., CustomerID in the Customers table), and a foreign key in another table references the primary key of the related table (e.g., CustomerID in the Orders table).

Think of it like this: You have a recipe (the query), and the ingredients are the tables. JOINs allow you to combine the ingredients (data) to create a delicious dish (information). Without JOINs, you'd only see the ingredients separately, missing the final product.

Understanding INNER JOIN

The INNER JOIN is the most common type of JOIN. It returns only the rows where there is a match in both tables based on the join condition (usually the foreign key matching the primary key).

Syntax:

SELECT column_list
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

Example:
Let's say we have two tables:

Customers Table:
| CustomerID | CustomerName | Email |
|------------|--------------|-------------------|
| 1 | Alice | alice@example.com |
| 2 | Bob | bob@example.com |

Orders Table:
| OrderID | CustomerID | OrderDate |
|---------|------------|--------------|
| 101 | 1 | 2023-10-26 |
| 102 | 2 | 2023-10-27 |

To find all orders with the customer's name, you'd use an INNER JOIN:

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers
ON Orders.CustomerID = Customers.CustomerID;

Result:
| OrderID | CustomerName | OrderDate |
|---------|--------------|--------------|
| 101 | Alice | 2023-10-26 |
| 102 | Bob | 2023-10-27 |

Understanding LEFT JOIN

LEFT JOIN returns all rows from the 'left' table (the table listed before the JOIN keyword) and the matching rows from the 'right' table. If there's no match in the right table, the columns from the right table will contain NULL values. This is useful when you want to see all data from one table, even if there isn't a corresponding entry in the other.

Syntax:

SELECT column_list
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

Example: Using the same tables (Customers and Orders) from the INNER JOIN example.

Now, let's say we have a Customer who hasn't placed an order yet. Using LEFT JOIN on the Customers table, we will get all Customers, and any orders they may have. 

SELECT Customers.CustomerName, Orders.OrderID, Orders.OrderDate
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;

Customers Table:
| CustomerID | CustomerName | Email |
|------------|--------------|-------------------|
| 1 | Alice | alice@example.com |
| 2 | Bob | bob@example.com |
| 3 | Charlie | charlie@example.com |

Orders Table:
| OrderID | CustomerID | OrderDate |
|---------|------------|--------------|
| 101 | 1 | 2023-10-26 |
| 102 | 2 | 2023-10-27 |

Result:
| CustomerName | OrderID | OrderDate |
|--------------|---------|--------------|
| Alice | 101 | 2023-10-26 |
| Bob | 102 | 2023-10-27 |
| Charlie | NULL | NULL |

Comparing INNER JOIN and LEFT JOIN

The key difference is what happens when there isn't a match. INNER JOIN only shows rows where both tables have a match. LEFT JOIN shows all rows from the left table and any matching rows from the right table, filling in NULL values where there's no match. Choosing between them depends on the specific question you're trying to answer. If you only care about matching data, use INNER JOIN. If you want to see all data from one table, even if there aren't matches in the other, use LEFT JOIN.

Progress
0%