go top

MySQL INNER and OUTER JOINs

Getting data from two tables in SQL is tricky for a beginner, but we will discuss how to use SQL JOINs to get data from multiple tables. The SQL keyword JOIN has a few different types, but in this tutorial, we are only going to use INNER JOIN and OUTER JOIN. We are using MySQL in the example, but JOINs are compatible across most types of SQL.

MySQL INNER JOIN gets matched records between two tables (matches explained below).
MySQL OUTER JOIN gets all records regardless of matches

Matches are rather difficult to describe so I will show you an example. Let's create two tables:

left_table:
Fields: table_id, name, zip_code
Type: int, varchar(100), int
Extra: auto_increment

right_table:
Fields: zip_code, country
Type: int, varchar(100)


SELECT *
FROM left_table l
INNER JOIN right_table r ON r.zip_code = l.zip_code


The INNER JOIN will return every record from the right_table that has at least one matching zip_code in the left_table. So, if you had two people in the left_table that had the same zip code that matched one record in the right_table and had a WHERE clause set to that particular zip_code, MySQL will return 2 records. The records will contain both of their names and the country they live in.

MySQL OUTER JOINs are similar to MySQL INNER JOINs, but have one main difference. Let's say we actually wanted all of the names, but still wanted to show their country. Obviously, we might have some people in left_table that won't have record in the right table, which means we won't know their country. For those cases, MySQL will return NULL values.


SELECT *
FROM left_table l
LEFT OUTER JOIN right_table r ON r.zip_code = l.zip_code


We use the LEFT keyword in the example to tell MySQL which table that we need all of the results. Our LEFT table is the left_table as it is specified first. After we use LEFT, the query looks exactly the same except for replacing INNER with OUTER. However, we drastically changed the results because we are now getting the entire left_table and only the matched results in the right_table.

MySQL LEFT INNER JOIN wouldn't make any sense as INNER JOINs find the commonality between the tables. However, you might see LEFT JOINs that do exactly the same thing as LEFT OUTER JOINs. Well, that is because they are one in the same. Writing OUTER in LEFT OUTER JOIN is a good practice and aid in your learning of SQL.

6 Comments to "MySQL INNER and OUTER JOINs"

  • Claudia Rogers

    Claudia Rogers

    May 23, 2014 at 12:30 pm

    I have always been confused about this. Thank you for enlightening me!

  • paul field

    paul field

    May 8, 2014 at 19:06 pm

    ok, what about if i had a table called PEOPLE with these columns:

    FIRSTNAME LASTNAME ADDRESS CITY STATE ZIPCODE

    and i wanted to find any "john smith" in the same zipcode as a "susan jones".... what would the query be?

    thanks!

    • paul field

      paul field

      May 8, 2014 at 19:13 pm

      I want to think its something like this, but it won't let both tables be the same one....

      SELECT * FROM left_table people LEFT OUTER JOIN right_table people ON r.zipcode = l.zipcode and l.first = 'john' and l.last = 'field' and r.first = 'list' and r.last = 'field';

    • vsanth

      vsanth

      May 6, 2018 at 10:00 am

      i have table user table booking table restaurant seats say 4 or 5 in table
      want to check how many seats of table free for a booking of user
      with left join and join mysql

  • sundar

    sundar

    February 28, 2014 at 13:47 pm

    Great article. nice information. I am learn for many information about your article. Thanks for sharing this information. dreamdestinations.in

  • rtytr

    rtytr

    January 24, 2014 at 15:52 pm

    height="80" width="100px"

Add your comment

Captcha
    • PHP Scripts

      Check our extensive collection of top-notch PHP Scripts that will enhance your website!

      Commercial PHP scripts
    • Free Scripts

      Add great new functionalities to your website with our Free Scripts collection.

      Free scripts