SQL Inner Join – How to Join Multiple Tables?

Jul 29 20232 min read
SQL Inner Join – How to Join Multiple Tables?

Overview

This kind of join logically connects 2 or more tables on the given condition and fetches you only the matching records from both the tables. Consider this join as Intersection in the mathematical set theory.

You can use 2, 3 or even more tables using this join but the resultant table will always contain the common records from both the sides.

Keyword INNER JOIN or JOIN is used to perform inner join. Both result the same way.

Following are the 3 tables which we are going to join in this post.

Customer, a customer table having 4 records.

+-------------+---------+------------------+
| customer_id | name    | email            |
+-------------+---------+------------------+
|         100 | Michael | michael@test.com |
|         101 | Robert  | robert@test.com  |
|         102 | John    | john@test.com    |
|         103 | Grey    | grey@test.com    |
+-------------+---------+------------------+

Orders, keeps orders from a customer. A customer may have 0, 1 or more orders.

+----------+-------------+---------------------+
| order_id | customer_id | order_date          |
+----------+-------------+---------------------+
|       10 |         100 | 2020-03-01 21:37:55 |
|       11 |         100 | 2020-03-01 21:37:55 |
|       12 |         101 | 2020-03-01 21:37:55 |
|       13 |         103 | 2020-03-01 21:37:55 |
|       14 |         106 | 2020-03-01 21:37:55 |
|       15 |         107 | 2020-03-01 21:37:55 |
|       16 |         108 | 2020-03-01 21:37:55 |
+----------+-------------+---------------------+

Invoice, keeps invoice for an order. An order may or may not have an invoice.

+------------+----------+---------------------+
| invoice_id | order_id | invoice_date        |
+------------+----------+---------------------+
|          1 |       11 | 2020-03-01 21:37:55 |
|          2 |       12 | 2020-03-01 21:37:55 |
|          3 |       13 | 2020-03-01 21:37:55 |
|          4 |       14 | 2020-03-01 21:37:55 |
|          5 |       15 | 2020-03-01 21:37:55 |
|          6 |       16 | 2020-03-01 21:37:55 |
|          7 |       17 | 2020-03-01 21:37:55 |
|          8 |       18 | 2020-03-01 21:37:55 |
+------------+----------+---------------------+

SQL Inner Join with 2 tables

Let’s join the tables customer and orders on the column customer_id, this will return customers with all their orders.

SELECT
  *
FROM customer c
INNER JOIN orders o
  ON c.customer_id = o.customer_id;

Result

+-------------+---------+------------------+----------+-------------+---------------------+
| customer_id | name    | email            | order_id | customer_id | order_date          |
+-------------+---------+------------------+----------+-------------+---------------------+
|         100 | Michael | michael@test.com |       10 |         100 | 2020-03-01 21:37:55 |
|         100 | Michael | michael@test.com |       11 |         100 | 2020-03-01 21:37:55 |
|         101 | Robert  | robert@test.com  |       12 |         101 | 2020-03-01 21:37:55 |
|         103 | Grey    | grey@test.com    |       13 |         103 | 2020-03-01 21:37:55 |
+-------------+---------+------------------+----------+-------------+---------------------+

Explanation

  • Joining column is customer_id.
  • Of all 4 customers, only 3 have orders i.e. 100, 101 and 103.
  • Customer 102 does not have any orders hence no records fetched for this customer.
  • Customer 100 has 2 orders hence 2 records.
  • Since we are selecting columns as *, all the columns from all the participating tables are fetching. You can select individual columns from each table as well.

SQL Inner Join with 3 tables

Let’s add 1 more table to the equation i.e. invoice. Each order is supposed to have an invoice. To make it simple, we will join the 3rd table to the existing query. This time it will return the rows which have matching records in all 3 tables.

SELECT
  *
FROM customer c
INNER JOIN orders o
  ON c.customer_id = o.customer_id
INNER JOIN invoice i
  ON o.order_id = i.order_id

Result

+-------------+---------+------------------+----------+-------------+---------------------+------------+----------+---------------------+
| customer_id | name    | email            | order_id | customer_id | order_date          | invoice_id | order_id | invoice_date        |
+-------------+---------+------------------+----------+-------------+---------------------+------------+----------+---------------------+
|         100 | Michael | michael@test.com |       11 |         100 | 2020-03-01 21:37:55 |          1 |       11 | 2020-03-01 21:37:55 |
|         101 | Robert  | robert@test.com  |       12 |         101 | 2020-03-01 21:37:55 |          2 |       12 | 2020-03-01 21:37:55 |
|         103 | Grey    | grey@test.com    |       13 |         103 | 2020-03-01 21:37:55 |          3 |       13 | 2020-03-01 21:37:55 |
+-------------+---------+------------------+----------+-------------+---------------------+------------+----------+---------------------+

Explanation

  • Invoice table is joined on the column order_id.
  • Existing query resulted records for order_id 10, 11, 12 and 13. And invoice table contains data for all but order_id 10, hence no record for order_id 10.

This is how INNER JOIN works. You can join more tables to the existing query but the thumb rule is same i.e. each table in joined to the previous table or the result of previous joins.