mySQL Table Joins

mySQL supports a number of different ways to join multiple tables together in your database queries. Which you should use depends on just what it is you are trying to retrieve. While there are a number of variants the types of joins basically form three groups.

CROSS JOIN

SELECT * FROM table1, table2 WHERE table1.name = table2.name AND ...

This is the simplest type of join and can be specified with just a comma between the table names (alternately you can specify JOIN or CROSS JOIN in place of the comma with no difference in the effect). In this type of join every row in each table is joined with every row in the other table and only the conditions specified in the WHERE clause limit which of those combinations are returned by the query.

A variant of this is a STRAIGHT_JOIN which ensures that the table referenced first is processed first. This allows you to force the processing order of the tables for where mySQL would otherwise have processed them less efficiently by choosing a different order.

You can join as many tables together as you wish using a cross join simply by specifying commas between the table names.

INNER JOIN

SELECT * FROM table1 INNER JOIN table2 ON table1.name = table2.name WHERE ...
SELECT * FROM table1 INNER JOIN table2 USING (name) WHERE ...

This type of join uses the ON or USING clause to identify which rows are to be joined. Only those rows where the join satisfied the ON or USING clause will be joined with the WHERE clause defining the subset of those rows of the joined tables that will be returned by the query.

This join will generally provide the same results as a cross join where the ON condition has been included in the WHERE clause but should achieve the result more efficiently unless the database optimizer recognises that the cross join is effectively asking for an inner join.

More than two tables can be joined together using inner joins by specifying INNER JOIN again after the ON or USING clause.

LEFT JOIN

SELECT * FROM table1 LEFT JOIN table2 ON table1.name = table2.name WHERE ISNULL(table2.name) AND ...
SELECT * FROM table1 LEFT JOIN table2 USING (name) WHERE ISNULL(table2.name) AND...
SELECT * FROM table1 NATURAL LEFT JOIN table2 WHERE ISNULL(table2.name) AND...

This type of join allows you to retrieve rows from the first table regardless of whether they do or do not have any matching entries in the second table. Adding the word OUTER between LEFT and JOIN is optional and makes no difference to the query. The NATURAL variant is equivalent to specifying USING and specifying all of the columns of both tables. By including an ISNULL test of a column from the second table that is being used to join the two tables together you can extract just those entries from the first table that do not have matching entries in the second table.

More than two tables can be joined using left joins the same way you can join more than two tables using inner joins. You can also combine inner join and left join in the same query. You cannot combine left joins with cross joins.

MySQL also supports RIGHT JOIN which works exactly the same as LEFT JOIN but with the tables specified in the opposite order. As other databases that support SQL do not support right joins you should not use them. Instead you should use left join and simply specify the tables in the opposite order.

 

This article written by Stephen Chapman, Felgall Pty Ltd.

go to top

FaceBook Follow
Twitter Follow
Donate