Joining MySQL tables

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

How much 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
• CROSS JOIN
• LEFT [OUTER] JOIN
• RIGHT [OUTER] JOIN
• STRAIGHT_JOIN

JOIN or INNER JOIN

The JOIN or INNER JOIN command is used to combine 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 combine 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 combine 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 combine 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 combine 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`);