Natural Joins

The keyword phrase NATURAL JOIN performs a join on two tables without needing an ON or WHERE clause to specify the relationship between the tables. Instead, MariaDB assumes that the tables are related by all the columns that have the same name in both tables.

Whether you can use NATURAL JOIN depends on the column-naming convention used. For instance, in the sample database are columns named product_code in both the products and order_lines tables. The relationship between these tables is suitable for a NATURAL JOIN, as shown in the following example:

MariaDB> SELECT p.name, ol.quantity, p.price
 -> FROM order_lines ol
 -> NATURAL JOIN products p
 -> WHERE order_id = 1;
+---------------+----------+-------+
| name | quantity | price |
+---------------+----------+-------+
| Small product | 4 | 5.99 |
| Large product | 2 | 15.99 |
+---------------+----------+-------+
2 rows in set (0.00 sec)

If the relationship between two tables relies upon a join that references columns that do not have the exact same name in both tables, you cannot use a NATURAL JOIN.