Category Archives: SQL

Differentiating INNER, LEFT, and RIGHT SQL JOINs

An SQL JOIN returns rows from multiple tables based on a common field between them. Consider the following users and orders tables:

users table

user_idfirst_namelast_nameemail
1JohnAdamsjohna@gmail.com
2MarkSmithmsmith@aol.com
3TomWilliamsontwilliamson@gmail.com

orders table

order_iduser_idtotaldate
10012500.992015-08-02
1002259.992015-08-03
10033602015-08-04

Typically JOIN is synonymous with INNER JOIN. Let’s perform an INNER JOIN on the users and orders table:

SELECT users.user_id, users.first_name, users.last_name, orders.total
FROM users
INNER JOIN orders
ON users.user_id = orders.user_id
ORDER BY users.user_id

Our result, which merges the users and orders tables by matching the user_id field, gets the user_id, first_name, last_name, and total, and finally sorts them by user_id:

user_idfirst_namelast_nametotal
2MarkSmith500.99
2MarkSmith59.99
3TomWilliamson60

An INNER JOIN is similar to the intersection of a set in mathematics, or the intersection of a Venn Diagram. The result includes all the rows with a match on users.user_id = orders.user_id. The result will not include any rows from the left or right table (users and orders, respectively) that do not contain a match within the ON clause.

INNER JOIN

A LEFT OUTER JOIN will return the same result as an INNER JOIN, however it will also include every row in the left table (users), even if there is no match within the ON clause. Fields that do not match will be marked with NULL:

SELECT users.user_id, users.first_name, users.last_name, orders.total
FROM users
LEFT OUTER JOIN orders
ON users.user_id = orders.user_id
ORDER BY users.user_id

user_idfirst_namelast_nametotal
1JohnAdamsNULL
2MarkSmith500.99
2MarkSmith59.99
3TomWilliamson60

LEFT OUTER JOIN

Likewise a RIGHT OUTER JOIN will include all of the rows from the right table (orders) and mark non-matches with a NULL:

user_idfirst_namelast_nametotal
2MarkSmith500.99
2MarkSmith59.99
3TomWilliamson60

RIGHT OUTER JOIN

In this particular example, every row in the right table (orders) has a match and no NULL fields are returned.

Finally, if not evident from the examples given above, it should be noted that LEFT and RIGHT refer to the order of the tables listed in the SQL statement. In our example, users is listed first (becoming the left table) and orders is listed next (effectively making it the right table).