---
id: 687ea8eba2a8b119f2ad9f8c
title: What Are the Different Ways to Join Tables?
challengeType: 19
dashedName: what-are-the-different-ways-to-join-tables
---

# --description--

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 five main types of `JOIN` operations:

- `INNER JOIN`
- `FULL OUTER JOIN`
- `LEFT OUTER JOIN`
- `RIGHT OUTER JOIN`
- `SELF JOIN`
- And `CROSS JOIN`

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

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

```sql
| 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:

```sql
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:

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

```sql
SELECT *
FROM products
FULL OUTER JOIN sales
  ON products.product_id = sales.product_id;
```

This is the result:

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

```sql
SELECT *
FROM products
LEFT JOIN sales
  ON products.product_id = sales.product_id;
```

This is the result:

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

```sql
SELECT *
FROM products
RIGHT JOIN sales
  ON products.product_id = sales.product_id;
```

Here is the result:

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

# --questions--

## --text--

Which SQL `JOIN` operation returns only the rows where there is a match in both tables based on the join condition?

## --answers--

`LEFT JOIN`

### --feedback--

Think about which join type is most restrictive in terms of only including records that match both tables.

---

`INNER JOIN`

---

`RIGHT JOIN`

### --feedback--

Think about which join type is most restrictive in terms of only including records that match both tables.

---

`FULL JOIN`

### --feedback--

Think about which join type is most restrictive in terms of only including records that match both tables.

## --video-solution--

2

## --text--

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.

## --answers--

`LEFT JOIN`

---

`INNER JOIN`

### --feedback--

Think about which join ensures that all records from the customers table are included in the output.

---

`RIGHT JOIN`

### --feedback--

Think about which join ensures that all records from the customers table are included in the output.

---

`FULL JOIN`

### --feedback--

Think about which join ensures that all records from the customers table are included in the output.

## --video-solution--

1

## --text--

Which SQL `JOIN` operation returns all rows from both tables, including unmatched rows (with `NULL`s for the columns of the table without a match)?

## --answers--

`LEFT JOIN`

### --feedback--

This join aims to be the most inclusive, taking all records from both tables.

---

`INNER JOIN`

### --feedback--

This join aims to be the most inclusive, taking all records from both tables.

---

`RIGHT JOIN`

### --feedback--

This join aims to be the most inclusive, taking all records from both tables.

---

`FULL JOIN`

## --video-solution--

4
