Joining MySQL tables

Posted on Wednesday December 07, 2011 / by Eric Potvin

left join, join, cross, inners, outer, right ... WAIT!

How many time someone asked your in an interview what is the difference between an INNER JOIN and RIGHT OUTER JOIN? or why the DBA asked you why you didn't use a RIGHT JOIN instead of a LEFT OUTER JOIN? Well here's what to answer to these questions.

JOIN or INNER JOIN

The JOIN or INNER JOIN command is used to combines non-null matching rows which are stored in related tables In order to join these tables, the join table require a common field (commonly called foreign key) from the left table. This type of join requires keywords ON or USING.

Example
SELECT
 *
FROM `table_a`
JOIN `table_b` ON (`table_a`.`id` = `table_b`.`fk_a_id`);

CROSS JOIN

The CROSS JOIN command is used to combines non-matching rows which are stored in related tables

Example
SELECT
 *
FROM `table_a`
CROSS JOIN `table_b`

WARNING! Use this type of join can return invalid records

LEFT [OUTER] JOIN

The LEFT [OUTER] JOIN command is used to combines null matching rows which are stored in related tables In order to join these tables, the join table require a common field (commonly called foreign key) from the left table. This type of join requires keywords ON or USING.

Example
SELECT
 *
FROM `table_a`
LEFT JOIN `table_b` ON (`table_a`.`id` = `table_b`.`fk_a_id`);

RIGHT [OUTER] JOIN

The RIGHT [OUTER] JOIN command is used to combines null matching rows which are stored in related tables In order to join these tables, the join table require a common field (commonly called foreign key) from the left table. This type of join requires keywords ON or USING.

Example
SELECT
 *
FROM `table_a`
RIGHT JOIN `table_b` ON (`table_a`.`id` = `table_b`.`fk_a_id`);

STRAIGHT_JOIN

The STRAIGHT_JOIN command is used to combines non-null matching rows which are stored in related tables. STRAIGHT_JOIN is similar to JOIN, except that the left table is always read before the right table. In order to join these tables, the join table require a common field (commonly called foreign key) from the left table. This type of join requires keywords ON or USING.

Example
SELECT
 *
FROM `table_a`
STRAIGHT_JOIN `table_b` ON (`table_a`.`id` = `table_b`.`fk_a_id`);