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.