Joins are used in mySQL to extract results from two(or more) tables with related data. INNER, RIGHT, and LEFT joins make it possible for a developer or database information to easily extract the information they need from a database. While they are a bit more difficult to understand, by using joins it takes some of the extra work load off of the server by doing multiple queries to retrieve the same information. Here I will show you the difference between these types of queries and how to use them properly.
Assume we have the following table structure in our database:
| PEOPLE | ||
|---|---|---|
| ID | Name | Gender |
| 1 | John Doe | Male |
| 2 | Jane Doe | Female |
| 3 | Mary | Female |
| FAVORITE_FOODS | ||
|---|---|---|
| ID | Meal | Person |
| 1 | Steak | 1 |
| 2 | Chicken Alfredo | 2 |
| 3 | Spaghetti | |
| 4 | Turkey Sandwich | 1 |
An inner join will return only the results that have matches in each table. SQL will use the INNER JOIN command by default if only JOIN is used in the syntax. An example of this type of query will look like this:
SELECT people.name, favorite_foods.meal FROM people INNER JOIN favorite_foods ON people.id = favorite_foods.person; |
This query will return only the results where the id from the people table matches a person from the favorite_foods table.
| Results | |
|---|---|
| people.name | favorite_foods.meal |
| John Doe | Steak |
| John Doe | Turkey Sandwich |
| Jane Doe | Chicken Alfredo |
As you can see the “Spaghetti” meal and “Mary” have been excluded from the results because they dont have any matches in the related table.
Outer Join
Outer joins are also referred to as right and left joins. The difference between an outer join and an inner join is that inner joins will not return results without a match in the second table, outer joins will
Right Outer Join
A right outer join, or right join, will return all of the results from the table specified AFTER the right join statement, and append any matches whether or not it has any.
SELECT people.name, favorite_foods.meal FROM people RIGHT JOIN favorite_foods ON people.id = favorite_foods.person; |
| Results | |
|---|---|
| people.name | favorite_foods.meal |
| John Doe | Steak |
| Jane Doe | Chicken Alfredo |
| Spaghetti | |
| John Doe | Turkey Sandwich |
Notice how the row for “Spaghetti” was returned even though it doesn’t have anything from the people table relating to it.
Left Outer Join
Left outer joins are similar to a right join in that it will return results whether they have a match or not. However, left joins extract all data from the table specified BEFORE the left join statement and all corresponding matches if any.
SELECT people.name, favorite_foods.meal FROM people LEFT JOIN favorite_foods ON people.id = favorite_foods.person; |
| Results | |
|---|---|
| people.name | favorite_foods.meal |
| John Doe | Steak |
| John Doe | Turkey Sandwich |
| Jane Doe | Chicken Alfredo |
| Mary | |
Just like the right join example, you can see that “Mary” was returned even though she doesn’t have a corresponding item in the favorite_foods table.
 |
SELECT * FROM table_a A LEFT JOIN table_b B ON A.key = B.key |
 |
SELECT * FROM table_a A RIGHT JOIN table_b B ON A.key = B.key |
 |
SELECT * FROM table_a A LEFT JOIN table_b B ON A.key = B.key WHERE B.key IS NULL |
 |
SELECT * FROM table_a A RIGHT JOIN table_b B ON A.key = B.key WHERE A.key IS NULL |
 |
SELECT * FROM table_a A INNER JOIN table_b B ON A.key = B.key |
 |
SELECT * FROM table_a A FULL OUTER JOIN table_b B ON A.key = B.key WHERE A.key IS NULL OR B.key IS NULL |
 |
SELECT * FROM table_a A FULL OUTER JOIN table_b B ON A.key = B.key |