curriculum/challenges/english/blocks/lecture-working-with-relational-databases/687ea8eba2a8b119f2ad9f8c.md
Even though relational databases organize data into separate tables, SQL's JOIN operations allow you to combine related information from tables to query data.
There are several variants of the JOIN operation, including:
INNER JOINFULL OUTER JOINLEFT OUTER JOINRIGHT OUTER JOINSELF JOINCROSS JOINLet's start with INNER JOIN.
An INNER JOIN filters the result to include only rows where the values in the joining columns that you specify are equal in both tables. Basically, it gives you the intersection of the data.
To illustrate this, let's say that we have two tables.
The first is this products table with multiple products. It includes their ID's, names, category, price, and origin.
| product_id | product_name | category | price (USD) | origin |
| ---------- | ---------------- | ----------- | ----------- | ------------- |
| 1 | Ice Cream | Food | 2.50 | India |
| 2 | Pizza Margherita | Food | 12.00 | Italy |
| 3 | Sushi | Food | 18.75 | Japan |
| 4 | T-Shirt | Clothing | 25.00 | USA |
| 5 | Jeans | Clothing | 60.00 | Argentina |
| 6 | Coffee | Beverages | 35.00 | France |
| 7 | Juice | Beverages | 5.00 | Colombia |
And second, we have a sales table, with the sale ID, product ID, quantity, and sale date.
| sale_id | product_id | quantity | sale_date |
| ------- | ---------- | -------- | ---------- |
| 101 | 1 | 2 | 2025-07-18 |
| 102 | 2 | 3 | 2025-02-13 |
| 103 | 6 | 10 | 2025-06-08 |
| 104 | 5 | 8 | 2025-01-10 |
| 105 | 2 | 1 | 2025-05-15 |
We can perform an INNER JOIN based on the product_id like this:
SELECT *
FROM products
INNER JOIN sales
ON products.product_id = sales.product_id;
This will only get the rows that have the same product_id in both tables. So if a product is not in the sales table or in products table, it will not be included.
This is the result:
product_id | product_name | category | price | origin | sale_id | product_id | quantity | sale_date
---------- | ---------------- | --------- |-------|-----------|---------|------------|----------|------------
1 | Ice Cream | Food | 2.50 | India | 101 | 1 | 2 | 2025-07-18
2 | Pizza Margherita | Food | 12.00 | Italy | 102 | 2 | 3 | 2025-02-13
2 | Pizza Margherita | Food | 12.00 | Italy | 105 | 2 | 1 | 2025-05-15
5 | Jeans | Clothing | 60.00 | Argentina | 104 | 5 | 8 | 2025-01-10
6 | Coffee | Beverages | 35.00 | France | 103 | 6 | 10 | 2025-06-08
We only see the rows of the products that have been sold. For example, 'Pizza Margherita' is in the sales table, with a product_id of 2, so we get that product in the result.
However, 'T-Shirts' with a product_id of 4 were not sold, so this product is not in the sales table. They are not in both tables, so they are not included in the result.
Joining tables does exactly what it sounds like. It joins two or more tables into one, which is why we see all the columns from both tables, including product_id twice.
A FULL OUTER JOIN returns all rows from both tables.
If a match is found in the specified columns, the data is combined and you get all columns for each matching record.
If there's no match in the specified columns, in either one of the tables, the missing columns are filled with NULL values.
Let's perform a FULL OUTER JOIN in our example, based on the product_id column. This column will determine if there is a match or not.
SELECT *
FROM products
FULL OUTER JOIN sales
ON products.product_id = sales.product_id;
This is the result:
product_id | product_name | category | price | origin | sale_id | product_id | quantity | sale_date
---------- | ---------------- | --------- |-------|-----------|---------|------------|----------|------------
1 | Ice Cream | Food | 2.50 | India | 101 | 1 | 2 | 2025-07-18
2 | Pizza Margherita | Food | 12.00 | Italy | 102 | 2 | 3 | 2025-02-13
3 | Sushi | Food | 18.75 | Japan | | | |
4 | T-Shirt | Clothing | 25.00 | USA | | | |
5 | Jeans | Clothing | 60.00 | Argentina | 104 | 5 | 8 | 2025-01-10
6 | Coffee | Beverages | 35.00 | France | 103 | 6 | 10 | 2025-06-08
7 | Juice | Beverages | 5.00 | Colombia | | | |
2 | Pizza Margherita | Food | 12.00 | Italy | 105 | 2 | 1 | 2025-05-15
Notice how 'Pizza Margherita' has all the data, including columns from the sales table because there was a match.
But 'T-Shirt' has empty (NULL) columns because no match was found in the sales table (this product was not sold).
A LEFT OUTER JOIN is used to get all the records from the left table and the matching information from the right table for each row of the left table.
If no match is found, the columns from the right table are filled in with NULL values.
Let's perform a LEFT OUTER JOIN in our example.
SELECT *
FROM products
LEFT JOIN sales
ON products.product_id = sales.product_id;
This is the result:
product_id | product_name | category | price | origin | sale_id | product_id | quantity | sale_date
---------- | ---------------- | --------- |-------|-----------|---------|------------|----------|------------
1 | Ice Cream | Food | 2.50 | India | 101 | 1 | 2 | 2025-07-18
2 | Pizza Margherita | Food | 12.00 | Italy | 102 | 2 | 3 | 2025-02-13
3 | Sushi | Food | 18.75 | Japan | | | |
4 | T-Shirt | Clothing | 25.00 | USA | | | |
5 | Jeans | Clothing | 60.00 | Argentina | 104 | 5 | 8 | 2025-01-10
6 | Coffee | Beverages | 35.00 | France | 103 | 6 | 10 | 2025-06-08
7 | Juice | Beverages | 5.00 | Colombia | | | |
2 | Pizza Margherita | Food | 12.00 | Italy | 105 | 2 | 1 | 2025-05-15
In this case, it's the same as the FULL OUTER JOIN because it includes all rows from the first table, products.
A RIGHT OUTER JOIN is very similar, but now we get all the records from the right table and the matching information from the left table for each row of the right table.
If there's no match, the columns from the left table are filled in with NULL values.
Let's perform a RIGHT OUTER JOIN in our example.
SELECT *
FROM products
RIGHT JOIN sales
ON products.product_id = sales.product_id;
Here is the result:
product_id | product_name | category | price | origin | sale_id | product_id | quantity | sale_date
---------- | ---------------- | --------- |-------|-----------|---------|------------|----------|------------
1 | Ice Cream | Food | 2.50 | India | 101 | 1 | 2 | 2025-07-18
2 | Pizza Margherita | Food | 12.00 | Italy | 102 | 2 | 3 | 2025-02-13
6 | Coffee | Beverages | 35.00 | France | 103 | 6 | 10 | 2025-06-08
5 | Jeans | Clothing | 60.00 | Argentina | 104 | 5 | 8 | 2025-01-10
2 | Pizza Margherita | Food | 12.00 | Italy | 105 | 2 | 1 | 2025-05-15
You'll notice that it has fewer rows. This is because it takes all the records from the right table (sales in this case), and this table has fewer rows than the products table.
If it finds a match in the products table, it fills those columns with the data. But if there's no match, the columns are filled with NULL.
In this case, every product that has been sold has a record in the products table, so the data is complete.
These are the most commonly-used JOIN operations, but there are two additional ones that you should know about.
A SELF JOIN allows you to join the table with itself. You can think of it as joining two copies of the same table. This is helpful for comparing different rows within the same table.
A CROSS JOIN, also known as a Cartesian Join, joins every row from the first table with every row of the second table. Therefore, it generates all possible row combinations. This operation doesn't need any conditions to join the tables.
These JOIN operations are fundamental for working with SQL. By choosing the right one, you can query the data you need as efficiently as possible.
Which SQL JOIN operation returns only the rows where there is a match in both tables based on the join condition?
LEFT JOIN
Think about which join type is most restrictive in terms of only including records that match both tables.
INNER JOIN
RIGHT JOIN
Think about which join type is most restrictive in terms of only including records that match both tables.
FULL JOIN
Think about which join type is most restrictive in terms of only including records that match both tables.
2
Which SQL JOIN operation would you use if you need to retrieve all customers (left) and, for each customer, any orders (right) they might have placed? Customers with no orders should still be included in the result.
LEFT JOIN
INNER JOIN
Think about which join ensures that all records from the customers table are included in the output.
RIGHT JOIN
Think about which join ensures that all records from the customers table are included in the output.
FULL JOIN
Think about which join ensures that all records from the customers table are included in the output.
1
Which SQL JOIN operation returns all rows from both tables, including unmatched rows (with NULLs for the columns of the table without a match)?
LEFT JOIN
This join aims to be the most inclusive, taking all records from both tables.
INNER JOIN
This join aims to be the most inclusive, taking all records from both tables.
RIGHT JOIN
This join aims to be the most inclusive, taking all records from both tables.
FULL JOIN
4