O'Reilly logo

Sams Teach Yourself MySQL by Chris Newman

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 p.name, ol.quantity, p.price
    ->  FROM order_lines ol
    ->  NATURAL JOIN products p
    ->  WHERE order_id = 1; +---------------+----------+-------+ | name | quantity | price | +---------------+----------+-------+ ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required