MySQL LEFT OUTER JOIN Tutorial & Examples

Here you find information about writing LEFT JOINs (also referred to as LEFT OUTER JOINs). This introduction into left joins includes a description, syntax information and example statements that use left outer joins. The Venn diagram on the left represents a result set that a statement with a left join produces. Please refer to the syntax examples below for an example. Links to additional information resources can be found at the end of this article.

Left Join syntax

First of all, some syntax examples for the impatient:

-- left join with USING-clause
SELECT *
FROM <leftTable> LEFT JOIN <rightTable>
USING(id)
-- left join with ON-clause
SELECT *
FROM <leftTable> a LEFT JOIN <rightTable> b
ON a.name = b.authorName

As you can see, a join condition can be written with the keyword ON or the keyword USING. The difference is that the ON keyword is used when each relationship column has a different name and USING when a column with the same name exists in both tables.

Reference table, left and right table?

When we join two tables, there is always a left and a right table (take a look at our syntax examples):

  • The left table is listed on the left side of the OUTER JOIN keywords
  • The right table is listed on the right side of the OUTER JOIN keywords

The outer join which is used decides which table is treated as the reference table. A left join treats the left- and a right join the right table as the reference table. Do you recognize the reference table in the syntax examples? Alright, fasten your seat belts. We’re ready to take off.

Left Outer Joins vs Inner joins

A left outer join is a specialized outer join. Like an inner join, an outer join combines (joins) matching rows that are stored in two different tables. In addition, an outer join also adds unmatched rows from a reference table to the result set. In case of a left outer join this means that when there is a row in the left table which can’t be combined with any row in the right table (according to the join condition), MySQL…

  • takes all selected values from the left table
  • combines them with the column names from the right table.
  • sets the value of every column from the right table to NULL

This is the important difference, because an inner join is not able to select records from a reference table that have no related data in another.

This entry was posted in Join Tutorials and tagged , , , , . Bookmark the permalink.

3 Responses to MySQL LEFT OUTER JOIN Tutorial & Examples

  1. ilan says:

    It is a common mistake to think that there is no difference putting the conditions in the ON clause and the WHERE clause.
    well, there is a big difference. The following post explains it with examples:
    http://www.mysqldiary.com/mysql-left-join/

  2. Pingback: JavaPins

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>