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 Ben 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.
Inner Join
The inner join is the default used when you don’t specify the 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 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.
Outer Join
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.
PostgreSQL
SELECT * FROM users
FULL OUTER JOIN orders ON users.id = orders.user_id;
MySQL
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 Ben 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 the joined table on the condition. If they do not match any rows in the joined table, they 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 table. As you can see Ben 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.