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, MySQL 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:

 mysql>  SELECT, 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.

Sams Teach Yourself MySQL in 10 Minutes
Sams Teach Yourself MySQL in 10 Minutes
ISBN: 0672328631
EAN: 2147483647
Year: 2006
Pages: 165
Authors: Chris Newman

Similar book on Amazon © 2008-2017.
If you may any questions please contact us: