Inner Joins

An inner join, or equijoin, is a join that uses a condition to specify a relationship between tables in which a column in one table is equal to another column in the other table. In the previous lesson, you learned how to do this using the WHERE clause.

The alternative syntax uses the keyword phrase INNER JOIN to specify a table to join to and the keyword ON to specify a conditional clause that indicates the relationship between the tables.

The following example reproduces the query from Lesson 11, "Joining Tables," to retrieve all the customer contact information:

 mysql> SELECT name,     -> CONCAT(last_name, ', ', first_name) as contact_name     -> FROM customers      -> INNER JOIN customer_contacts     -> ON customers.customer_code =     ->    customer_contacts.customer_code     -> ORDER BY name, contact_name; +-------------------------+---------------------+ | name                    | contact_name        | +-------------------------+---------------------+ | Musicians of America    | Britten, Benjamin   | | Musicians of America    | Gershwin, George    | | Musicians of America    | Lennon, John        | | Presidents Incorporated | Lincoln, Abraham    | | Presidents Incorporated | Nixon, Richard      | | Presidents Incorporated | Roosevelt, Franklin | | Presidents Incorporated | Roosevelt, Theodore | | Science Corporation     | Curie, Marie        | | Science Corporation     | Darwin, Charles     | | Science Corporation     | Einstein, Albert    | | Science Corporation     | Franklin, Benjamin  | +-------------------------+---------------------+ 11 rows in set (0.00 sec) 

The keywords INNER JOIN appear immediately after the FROM clause and before any filtering is specified. When you use JOIN, the relationship between tables is defined after using the ON keyword instead of WHERE. This syntax for a join is longer, but often it is more readable because the WHERE clause is used only to filter the resulting data set.

Check back to the corresponding example in the previous lesson, and you will see that its WHERE clause is identical to the ON clause given previously.

Using JOIN

Whether you put multiple tables in the FROM clause and specify the join condition in the WHERE clause, or use JOIN ... ON is a matter of preference. Use whichever format you feel most comfortable with.