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