MySQL OUTER JOIN Tutorial & Examples

Here you find information about OUTER JOINs in MySQL statements. This tutorial is a general introduction. Syntax details and example statements are split into a LEFT OUTER JOIN tutorial and RIGHT OUTER JOIN tutorial because these are specific outer joins. 

Outer Join basics

MySQL supports outer joins. They identify matching rows that are stored in two different tables and add the selected columns to the result set. That’s exactly what an inner join does as well.

The difference between inner and outer join is: An outer join can identify rows without a match in the joined table. When no match was found, MySQL sets the value of columns from the joined table to NULL. This is important for example when you want to select all records from a reference table that have no related data in another:

  • select all orderids from a table “orders” where no entry in a table “shipping” exists
  • delete records from a table if they have no related records in another table
  • and things like that.

To understand which table is treated as the reference table, we first need to understand which table is called the “left table” and which table is called the “right table”. It’s actually as easy as:

  • 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

Here is an example:

SELECT *
FROM leftTable LEFT JOIN rightTable
USING(id)

Left Join or Right Join?

To indicate the reference table in an outer join statement, MySQL separates between LEFT JOIN and a RIGHT JOIN. The left table is the reference table in a LEFT JOIN and right table in a RIGHT JOIN. Please see the following links for more information about the specific outer joins:

Just keep in mind: a left join can also be written as a right join. They basically answer the same questions, you just have to ask. However, it’s generally common to write an outer join as a left join in case you read normal text left-to-right as well.

External Resources

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

8 Responses to MySQL OUTER JOIN Tutorial & Examples

  1. Pingback: MySQL INNER JOIN Tutorial & Examples » MySQL Join Tutorials

  2. Jonatan says:

    Hello,

    I still can’t understand how to do something very simple, so I ask you for a hint :-)
    I have three tables : one with orders, one with bills and one with offers.
    Links between them are unsignificant, except the date.

    So today I have :

    ORDERS :
    DateOrder, AmountOrder

    BILLS :
    DateBill, AmountBill

    OFFERS :
    DateOffers, AmountOffer

    I would like to merge the date of the three tables in order to get :
    Date, AmountOrder, AmountBill, AmountOffer

    The data will look like :

    Date AmountOrder AmountBill AmountOffer
    2011-08-18 1000.00
    2011-09-11 2500.00
    2011-11-09 235.00 350.00

    The merging between two dates (like here, two records on 2011-11-09) is not necessary, but I would like to have a three-times-longer table with all the dates and the corresponding amount in the right column.

    What would you suggest ?
    I have tried several ways but I could get nothing that worked as I wanted to.

    Thanks !
    Jonatan

  3. jbrinkmann says:

    Hi. First of all thanks a lot for your comment! I really appreciate it :)

    To answer your question:
    I would suggest using unique primary keys for all rows, like:

    - ORDERS.order_id
    - BILLS.billing_id
    - OFFERS.offer_id

    To store the relationship between ORDERS and BILLS (I assume a 1:1 relationship because 1 order probably has one bill?) you could store the billing_id in an additional ORDERS column.

    For n:m you probably want to create another table to store the relationship for you, i.e.:

    offers_bills
    - order_id
    - billing_id

    And for 1:n you can store the primary key in the n-side table. If you for example take the following two tables:

    - order
    - orderposition

    You would have a 1:n relationship. The rows in the orderposition table store the order_id of the order table.

    I don’t know which relationship between OFFERS and the other table exists (i.e. 1:n or n:m?). Therefor you have to decide which solution fits best.

    If you build your joins based on the date it will probably cause problems. It’s possible that two orders are stored within the same second.

  4. Mehdi says:

    Thank you.

  5. Jarrod says:

    Thanks a lot! You’ve saved me; I had never really understood joins, and this helped plenty :)

  6. 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>