Joining Multiple Tables

To join multiple tables using the JOIN ... ON syntax, each table has its own JOIN keyword and its own ON clause immediately afterward.

The following example reproduces the query from Lesson 11 that retrieves the order history for a given customer, joining the orders,order_lines, and products tables:

MariaDB> SELECT o.order_id, o.order_date, l.quantity, p.name
 -> FROM orders o
 -> INNER JOIN order_lines l
 -> ON o.order_id = l.order_id
 -> INNER JOIN products p
 -> ON p.product_code = l.product_code
 -> WHERE o.customer_code ='SCICORP'
 -> ORDER BY o.order_date;
+----------+------------+-------------+--------------------+
| order_id | order_date | quantity | name |
+----------+------------+-------------+--------------------+
| 3 | 2006-01-23 | 16 | Small product |
| 4 | 2006-02-02 | 16 | Small product |
| 4 | 2006-02-02 | 10 | Large product |
| 5 | 2006-02-05 | 10 | Medium product |
| 5 | 2006-02-05 | 10 | Large product |
+----------+------------+-------------+----------------+
5 rows in set (0.00 sec)

In this example, the orders table appears in the FROM clause, and order_lines and products are each given after an INNER JOIN keyword. This is the logical order in which to write the query because it has a hierarchical structureorders contain order lines, which, in turn, reference products. The orders table is referenced in the WHERE clause.

However, when using INNER JOIN, the tables can be specified in any order without affecting the outcome of the query.

Inner Joins

The majority of joins you will want to perform are inner joins, so this is the default behavior for the JOIN keyword. You can use JOIN on its own instead of INNER JOIN, and your query will work the same way.


The ON Clause

The ON clause works just like a WHERE clause but is specific to a join condition. If you need to, you can add extra conditions to the ON clause using AND, just as you can with WHERE.


Previous Page Next Page