Retrieve Data From Multiple Tables in MySQL – JOIN in MySQL

Today we’ll discuss queries to retrieve data from multiple tables in MySQL. In this tutorial, we’ll also learn how to use JOIN in MySQL.

When displaying data stored in the database, we will often take the data from multiple tables and processed in such way so that produce a single output, e.g. creating a sales reports.

In the relational databases model (RDBMS) including MySQL, data collection from multiple tables can be done easily because each table has relation, the form of relationship are various, it can be one-to-one relationship, one-to-many, and many-to- many

Preparation

Before going any further, we need to set up tables that we will use in this tutorial.

In this example, there are four tables, namely: customers, products, transactions, and transaction_details. the structure and relationship of the tables look like the following figure:

Table Relation

For simplicity, we are not going to use all of those tables,  we’ll only use some of them instead. The examples of the data are:

customer table:

+-------------+---------------+-------------------+
| customer_id | customer_name | email             |
+-------------+---------------+-------------------+
|           1 | Alfa          | alfa@yahoo.com    |
|           2 | Beta          | beta@yahoo.com    |
|           3 | Charlie       | charlie@gmail.com |
|           4 | Delta         | delta@gmail.com   |
+-------------+---------------+-------------------+

sales_order table:

+----------------+-------------+------------+--------------+
| sales_order_id | customer_id | order_date | order_amount |
+----------------+-------------+------------+--------------+
|              1 |           1 | 2017-02-22 |           23 |
|              2 |           3 | 2017-02-22 |           19 |
|              3 |           2 | 2017-01-01 |          171 |
|              4 |           1 | 2017-02-04 |           31 |
|              5 |        NULL | 2017-02-10 |            8 |
+----------------+-------------+------------+--------------+

I. JOIN In MySQL

To combine tables in MySQL, we use a JOIN clause. In MySQL, there are three forms of join, that are INNER JOIN, LEFT OUTER JOIN and RIGHT OUTER JOIN.

The general syntax of join looks like the following:

SELECT column_list
FROM table
INNER JOIN | LEFT OUTER JOIN | RIGHT OUTER JOIN table ON conditions

In addition to using the ON clause to define the conditions, we can also use the USING clause, the syntax looks like follows:

SELECT column_list
FROM table
INNER JOIN | LEFT OUTER JOIN | RIGHT OUTER JOIN table USING (column_list)

USING clause will use data in the column_list to connect both tables, so this column_list must exist in the both tables and must have the same name.

Usually, associated columns between tables are defined as the Primary Key or Foreign Key, but it does not matter if they are not in the case.

II. INNER or CROSS JOIN

The first method to combine tables in MySQL is using the inner join clause. With the inner join, the tables will be joined using values that exist on both tables using defined columns. Illustrated in a Venn diagram form, the form of inner join looks like the following figure:

Retrieve Data From Multiple Tables in MySQL - Inner Join Illustration

In MySQL, we can define the INNER JOIN clause in three ways: (1) using the INNER JOIN clause (2) Using CROSS JOIN clause (3) using only JOIN clause. You are free to choose one of them and should consistent in it, I personally prefer using just JOIN clause.

For example, we’ll display the customers who make orders, run the following query:

SELECT c.customer_id, customer_name, order_date, order_amount
FROM customer c
JOIN sales_order s ON c.customer_id = s.customer_id

If we use the USING clause, then the query will look like the following:

SELECT c.customer_id, customer_name, order_date, order_amount
FROM customer c
JOIN sales_order s USING (customer_id)

The results:

+-------------+---------------+------------+--------------+
| customer_id | customer_name | order_date | order_amount |
+-------------+---------------+------------+--------------+
|           1 | Alfa          | 2017-02-22 |           23 |
|           3 | Charlie       | 2017-02-22 |           19 |
|           2 | Beta          | 2017-01-01 |          171 |
|           1 | Alfa          | 2017-02-04 |           31 |
+-------------+---------------+------------+--------------+

Explanations:

  • Customers named Delta does not appear in the result table, it is because the customer does not make any orders.
  • Order with order_id 5 also does not appear, this is because that order has customer_id NULL, so it is not connected to any customer.

II. OUTER JOIN

The second method to combine multiple tables in MySQL is using an outer join. On the outer join, MySQL will display all data in one table, while for the other table, the will only be display if it exists in the first table.

In MySQL, there are two kinds of OUTER JOIN: LEFT OUTER JOIN and RIGHT OUTER JOIN.

1 LEFT OUTER JOIN

On LEFT OUTER JOIN, MySQL will display all data in the left table, while for the right table, MySQL will only display the data if values in the joined columns exist on the left table.

Illustrated in a Venn diagram form, the form of LEFT OUTER JOIN looks like the following figure:

Left Outer Join Illustration

We can define LEFT OUTER JOIN in two ways (1) using LEFT OUTER JOIN clauses, (2) using just LEFT JOIN clause, you is free to choose one of them and should consistent on it, I personally prefer to use the second form because it is more simple.

For example, we’ll show all customers with their orders, run the following query:

SELECT c.customer_id, customer_name, order_date, order_amount
FROM customer c
LEFT JOIN sales_order USING(customer_id)

The result:

+-------------+---------------+------------+--------------+
| customer_id | customer_name | order_date | order_amount |
+-------------+---------------+------------+--------------+
|           1 | Alfa          | 2017-02-22 |           23 |
|           3 | Charlie       | 2017-02-22 |           19 |
|           2 | Beta          | 2017-01-01 |          171 |
|           1 | Alfa          | 2017-02-04 |           31 |
|           4 | Delta         | NULL       |         NULL |
+-------------+---------------+------------+--------------+

From the example above, we know that with LEFT JOIN, all the data in left table (the customer table) will be displayed, while the data in the right table (the sales_order table) will only be displayed if the value in the customer_id column exists in the customer table, that are: 1, 2, and 3

2. RIGHT OUTER JOIN

Opposite to  LEFT OUTER JOIN, in the RIGHT OUTER JOIN, MySQL will display all data in the right table, while for the left table, MySQL will only display the data if values in the joined columns exist on the right table.

The Venn diagram of RIGHT OUTER JOIN:

Right Outer Join Illustration

Just like LEFT OUTER JOIN, we can also define RIGHT OUTER JOIN  in two ways, using RIGHT OUTER JOIN clause or just RIGHT JOIN clause. I personally prefer to use RIGHT JOIN clause.

For example, we’ll show all the orders data along with it’s customer, run the following query:

SELECT c.customer_id, customer_name, order_date, order_amount
FROM customer c
RIGHT JOIN sales_order USING (customer_id)

The results:

+-------------+---------------+------------+--------------+
| customer_id | customer_name | order_date | order_amount |
+-------------+---------------+------------+--------------+
|           1 | Alfa          | 2017-02-22 |           23 |
|           1 | Alfa          | 2017-02-04 |           31 |
|           2 | Beta          | 2017-01-01 |          171 |
|           3 | Charlie       | 2017-02-22 |           19 |
|        NULL | NULL          | 2017-02-10 |            8 |
+-------------+---------------+------------+--------------+

In the example above, we know that all the data in the right table (sales_order table) are displayed, while the data in the left table (the customer table) will only be displayed if the value in the customer_id column exists in the sales_order table, that are: 1, 2, and 3

Is RIGHT JOIN Really Needed?

Actually, right join only move the position of the table, from left to right. We change the right join query into left join query by just changing the position of the table, consider the following example:

SELECT c.customer_id, customer_name, order_date, order_amount
FROM sales_order c
LEFT JOIN customer USING (customer_id)

Notice in the example above, we switch the position of the sales_order and customer tables. If the query is executed, we’ll get the following result:

+-------------+---------------+------------+--------------+
| customer_id | customer_name | order_date | order_amount |
+-------------+---------------+------------+--------------+
|           1 | Alfa          | 2017-02-22 |           23 |
|           1 | Alfa          | 2017-02-04 |           31 |
|           2 | Beta          | 2017-01-01 |          171 |
|           3 | Charlie       | 2017-02-22 |           19 |
|        NULL | NULL          | 2017-02-10 |            8 |
+-------------+---------------+------------+--------------+

Note that the above results exactly same as the results in the right join example, so, in order to make it simpler and easier, just use one form of the outer join, LEFT JOIN, or RIGHT JOIN, I personally prefer to use LEFT JOIN.

III. IMPLICIT JOIN

So far, we retrieve data from multiple tables in MySQL using the JOIN clause.

In addition, there is one more method to combine MySQL tables, that is using implicit join, it called implicit join because there is no JOIN clause here, the implicit join define relationships between tables in the WHERE clause.

For example, let’s combine customer and sales_order tables, run the following query:

SELECT c.customer_id, customer_name, sales_order_id, order_date, order_amount
FROM customer c, sales_order s
WHERE c.customer_id = s.customer_id

The results:

+-------------+---------------+----------------+------------+--------------+
| customer_id | customer_name | sales_order_id | order_date | order_amount |
+-------------+---------------+----------------+------------+--------------+
|           1 | Alfa          |              1 | 2017-02-22 |           23 |
|           3 | Charlie       |              2 | 2017-02-22 |           19 |
|           2 | Beta          |              3 | 2017-01-01 |          171 |
|           1 | Alfa          |              4 | 2017-02-04 |           31 |
+-------------+---------------+----------------+------------+--------------+

Note that the results are identical to the results in the INNER JOIN example, so it can be concluded that implicit join = inner join.

Implicit join requires both tables have the same value ( WHERE c.customer_id = s.customer_id ), thus implicit join can only be applied in the INNER JOIN,  not for the OUTER JOIN.

Implicit join is an old method to perform join, it exists in the first SQL standard. After emerging newer standards (SQL2) the JOIN clause is introduced. I personally prefer to use JOIN clause because it is easier to read and understand, especially the relation between tables.

In the JOIN clause, relationships between tables are presented in the ON or USING clause, while the data filter is in the WHERE clause, for example:

SELECT c.customer_id, customer_name, sales_order_id, order_date, order_amount
FROM customer c
LEFT JOIN sales_order s USING(customer_id)
WHERE c.customer_id = 2 OR s.customer_id = 1

while in the implicit join, relationships between tables and data filter are defined in the WHERE clause, for example:

SELECT c.customer_id, customer_name, sales_order_id, order_date, order_amount
FROM customer c, sales_order s
WHERE c.customer_id = s.customer_id
AND (c.customer_id = 2 OR c.customer_id = 1)

In this tutorial, we have learned how to retrieve data from multiple tables in MySQL.

Subscibe Now

Loves articles on webdevzoom.com? join our newsletter to get quality article right to your inbox. Nothing else, just quality stuff!!!

Leave a comment

Like Us

Newsletter

Great information from webdevzoom.com right to your inbox

We value your privacy

Social