Joining MySQL tables
Thursday, December 8, 2011 EST
by: Eric Potvin
Tags:mysql
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
- CROSS JOIN
- LEFT [OUTER] JOIN
- RIGHT [OUTER] JOIN
- STRAIGHT_JOIN
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`);
Link to this Article
To link directly to this article from your web site, use one of the following snippets below.
Joining MySQL tables | Book Of Zeus<a href="http://www.bookofzeus.com/articles/joining-mysql-tables/" title="Joining MySQL tables">Joining MySQL tables | Book Of Zeus</a>
Short URL:
Joining MySQL tables | Book Of Zeus<a href="http://s.bookofzeus.com/ZAp6k" title="Joining MySQL tables Short URL">Joining MySQL tables | Book Of Zeus</a>