Portable SQL : Statements : Data retrieval : Join syntax

From Linuxnetworks

SQL supports several ways to join the rows of two or more tables. In the examples of the following sections are only two tables used for simplicity but in everydays use it's often required to join more tables. The principle is the same as in the examples.

[edit] Cartesian product

SELECT column-list
FROM ( table1, table2 )
WHERE conditions

The cartesian product combines every row from table1 with all rows from table2 which results in table1*table2 rows. Usually, that's not a desired result and most often returned by accident if you forgot to add the correct assignment of the rows as condition in the WHERE clause. Because of this potential source of errors, this syntax is not recommended.

The parentheses around the table names in the FROM clause are necessary for portable SQL because MySQL requires them from version 5 on.

[edit] Inner join

SELECT column-list
FROM table1 AS t1
[INNER] JOIN table2 AS t2 ON ( t1.key = t2.key )
WHERE conditions

Inner joins are most often used in SQL statements. They combine all records from table1 with all records from table2 where the key from the first table has the same value as the foreign key from the second table. This SELECT type may return no rows if there are no keys that match and is very efficient if the primary key in table1 and the appropriate foreign key in table2 is used.

Further conditions limiting the rows from the first or the second table should be placed in the WHERE clause and not in the ON clause of the inner join. Some database implementations might have (optimization) problems with this.

[edit] Left join

SELECT column-list
FROM table1 AS t1
LEFT JOIN table2 AS t2 ON ( t1.key = t2.key )
WHERE conditions

Contrary to inner joins, left joins even return rows if there are not matching rows in the second table. In this case, the columns from the second table in the column list are filled with NULL values.

If you want to limit the the joined rows from the second table, it's very convenient to add the conditions in the ON clause of the JOIN. At least MySQL 4 seems to be buggy in this case and sometimes returns no rows at all. As a workaround you may add the condition to the WHERE clause but have to check for NULL values first:

SELECT column-list
FROM table1 AS t1
LEFT JOIN table2 AS t2 ON ( t1.key = t2.key )
WHERE ( t2.column IS NULL OR t2.column = value )

This isn't perfect as MySQL doesn't use indices any more after the OR composition so this part should be placed at the end of the WHERE clause.