SQL JOIN Explanation

As I am trying to learn SQL, JOIN is a clause worth mentioning. There are several kinds of JOIN, including SQL FULL OUTER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, INNER JOIN. In this entry, I am trying to explain SQL JOIN, using examples and diagrams.

What is JOIN?
JOIN operation combines rows from two or more tables. Let’s take a look at an example:

Think about a clothing store that wants to keep track of customers’ orders. Supposed I have two tables: Users and Orders.

Users (Table 1 ) id | name --------------- 1 | Bob 2 | Alice 3 | John 4 | Mike Orders (Table 2) id | user_id | name ----------------------------------------- 1 | 1 | T-Shirt 2 | 1 | Khakis 3 | 2 | T-Shirt 4 | 2 | Jeans 5 | 2 | Hoodies 6 | 3 | Shoes 7 | NULL | Watch

1. Inner Join
Inner join is the default Join operation if nothing else is specified. This join returns the records that match in both tables.

SELECT * FROM Users INNER JOIN Orders ON Users.id = Orders.user_id;

The output of the query is:

id | name | id | user_id | name --------------------------------------------------------------- 1 | Bob | 1 | 1 | T-Shirt 1 | Bob | 2 | 1 | Khakis 2 | Alice | 3 | 2 | T-Shirt 2 | Alice | 4 | 2 | Jeans 3 | Alice | 5 | 2 | Hoodies 4 | John | 6 | 3 | Shoes

As we can see, the query puts two tables along side, selecting the records from both tables in which id in Table 1 is equal to user_id in Table 2. In other words, this query answers the question: For those in Table 1 who ordered something, what did they order? Note that User with id = 4 (Mike) who didn’t buy anything and Order with id = 7 (Watch) which didn’t belong to any user are not included in the output.

A Venn diagram also helps:

2. Full Outer Join
Full Outer Join takes all records that matches from EITHER table.

SELECT * FROM Users FULL OUTER JOIN Orders ON Users.id = Orders.user_id;

The output of the query is:

id | name | id | user_id | name --------------------------------------------------------------- 1 | Bob | 1 | 1 | T-Shirt 1 | Bob | 2 | 1 | Khakis 2 | Alice | 3 | 2 | T-Shirt 2 | Alice | 4 | 2 | Jeans 2 | Alice | 5 | 2 | Hoodies 3 | John | 6 | 3 | Shoes 4 | Mike | NULL | NULL | NULL NULL | NULL | 7 | NULL | Watch

FULL OUTER JOIN gets records that match either Table 1 or Table 2. If the record selected only match Table 1 but not Table 2, the records in Table 2 is shown as NULL and vice versa. Venn diagram:

3. Left Outer Join
Left Outer Join returns records that match the left table in the SQL query:

SELECT * FROM Users LEFT OUTER JOIN Orders ON Users.id = Orders.user_id;

The output of the above query is:

id | name | id | user_id | name --------------------------------------------------------------- 1 | Bob | 1 | 1 | T-Shirt 1 | Bob | 2 | 1 | Khakis 2 | Alice | 3 | 2 | T-Shirt 2 | Alice | 4 | 2 | Jeans 2 | Alice | 5 | 2 | Hoodies 3 | John | 6 | 3 | Shoes 4 | Mike | NULL | NULL | NULL

This query answers the question: What did people in Table 1 order? It returns everything that matches the left table in the query, which is the Users table (Table 1) in this case. We can see in the output of the query there is one who didn’t buy anything (Mike). Venn diagram:

4. Right Outer Join
Right Outer Join is the same as Left Outer Join, except that a Right Outer Join query takes records that match the right table, in this case the Orders table.

SELECT * FROM Users RIGHT OUTER JOIN Orders ON Users.id = Orders.user_id;

The output of the above query is:

id | name | id | user_id | name --------------------------------------------------------------- 1 | Bob | 1 | 1 | T-Shirt 1 | Bob | 2 | 1 | Khakis 2 | Alice | 3 | 2 | T-Shirt 2 | Alice | 4 | 2 | Jeans 2 | Alice | 5 | 2 | Hoodies 3 | John | 6 | 3 | Shoes NULL | NULL | 7 | NULL | Watch

Here is the diagram of Right Outer Join: