Combine Data from Various Tables

Join or inner join

In this lesson, we’ll write a select statement that will display the combined information from all the tables. So, we’ll see the customers in one result set with their corresponding orders, order details, and products. The select command used to achieve that result isn’t complex. The select query is only long because we want to combine the information from many tables. Combining information from two tables is done with the join technique, which relies on the foreign keys between the tables.

Joining customers with orders

If we want to combine the information between customers and orders, we start with the following:

select * from customers join orders;

This tells MySQL that we want to combine the information from the customers table with the information from the orders table. Giving only the table names isn’t enough for join. We need to tell MySQL which columns we want to use the on clause.

Note: We can use the join construct to combine information from two tables without using the foreign key relationship that they may have, but this is uncommon.

We usually specify that the foreign key relationship is the one that should be used for the joining:

select * from customers join orders on customers.id = orders.customer_id;

The code above is the correct statement to join the information from the customers table to the orders table.

Let’s execute this statement.

mysql> select * from customers join orders on customers.id = orders.customer_id;
+----+-----------+----------+----+--------------+---------------------+-------------+
| id | name      | identity | id | order_number | ordered_at          | customer_id |
+----+-----------+----------+----+--------------+---------------------+-------------+
|  4 | John Woo  | JW001    |  2 | ABC001       | 2016-09-12 21:36:56 |           4 |
|  5 | Maria Foo | MF001    |  3 | ABC002       | 2016-09-12 21:40:20 |           5 |
|  5 | Maria Foo | MF001    |  4 | ABC003       | 2016-09-12 21:44:34 |           5 |
|  6 | Jim Papas | JP001    |  5 | ABC004       | 2016-09-12 21:46:38 |           6 |
|  6 | Jim Papas | JP001    |  6 | ABC005       | 2016-09-12 21:47:41 |           6 |
|  6 | Jim Papas | JP001    |  7 | ABC006       | 2016-09-12 21:49:31 |           6 |
+----+-----------+----------+----+--------------+---------------------+-------------+
6 rows in set (0.04 sec)

We now have the combined rows of the customers and the orders tables. Each row from the customers table is being matched to a row from the orders table using the combination logic customers.id = orders.customer_id. In other words, a given customers row is matched to the orders row only if the customer_id column on the orders table has the same value as the id column of the given row in customers table.