SQL Joins Explained
When combining rows from multiple tables in one query, you need to use the
JOIN command. There are a few different types of joins, and the following should help explain the differences between them.
The syntax will vary depending on which database type you are using. For more details, see the documentation for your database, linked below.
In this example, I have a restaurant database with two tables. One table is for Users (or customers) and the other for Orders.
Notice that in this dataset Justin and Praveen didn't order anything, and that there is a strange user #99 in the Orders table that doesn't exist in the Users table.
The inner join is the default used when you don't specify what type of Join. When you do an inner join of two tables it returns a new set of data with all of the instances of of the join where the condition was met. If the condition was not met between the tables, the rows are ignored. This type of join will result in the smallest number of results.
SELECT * FROM users INNER JOIN orders ON users.id = orders.user_id;
The result is a combination of the two tables where the rows have been joined by their common user_ids.
You can see that because Justin and Praveen did not order anything, they did not show up in the results for this join. Additionally, user #99 who is in the order table, but not listed in the users table did not show up in the result either.
Where an inner join ignores any rows that don't match the condition, an outer join can still show results if no condition was met. For example even though Justin and Praveen did not order anything, they could still show up in the results of an outer join query, depending on which table is favored. There are three options for favoring tables, and therefore there are three possible outer joins.
Full Outer Join
A full outer join means that both tables are favored, so all rows from each table will be listed in the result regardless of whether they match any rows in the other table. In practice these joins are fairly rare.
SELECT * FROM users FULL OUTER JOIN orders ON users.id = orders.user_id;
MySQL does not have a command for Full Outer Joins. You can, however, replicate it easily with the UNION command by combining the results of the left outer and a right outer joins.
SELECT * FROM users LEFT OUTER JOIN orders ON users.id = orders.user_id UNION SELECT * FROM users RIGHT OUTER JOIN orders ON users.id = orders.user_id
As you can see, Praveen and Justin are listed as well as the mysterious user #99.
Left Outer Join
The left outer join means that the join will favor the left listed (first listed) table. Favoring the table means that all results from that table will be shown in the result, whether or not they match of the joined table on the condition. If they do not match any rows in the joined table, it will be attached to null columns.
SELECT * FROM users LEFT OUTER JOIN orders ON users.id = orders.user_id;
Here we've listed the users table first, so it is the favored tables. As you can see Justin and Praveen, who were not present in the results of the inner join as they did not order anything, are still listed in the results of this outer join. The columns that would normally be filled with values from the joined table are simply nulled.
Right Outer Join
The right outer join is the same as the left, except the favored table is the right listed (second listed) table.
SELECT * FROM users RIGHT OUTER JOIN orders ON users.id = orders.user_id;
You can see that by favoring the orders table we now see the unmatched user #99 but not Justin and Praveen.