Jamie Balfour

Welcome to my personal website.

Find out more about me, my personal projects, reviews, courses and much more here.

Part 4.3Left and right joins

Part 4.3Left and right joins

There are two other kinds of join in SQL, left and right.

Left and Right Joins in SQL

Venn Diagram

With the original Venn Diagram as pictured, the Left Join means everything in Table 1 and everything matching in Table 2.

A Right Join would mean everything matching in Table 1 and everything in Table 2.

The following tables are used in these examples:


product_id product_name
001 Intel Core i7 2600
002 Intel Core i5 2400
003 Intel Core i3 2120


product_id price
001 $270.00
001 $252.00
002 $120.00
002 $190.00

A Right Join which selects from both tables would look like:

SELECT `product_name`, `price` FROM `Purchases` p RIGHT JOIN `Product_Info` i ON p.`product_id` = i.`product_id`

Using the above statement will return the following table:

product_name price
Intel Core i7 2600 $270.00
Intel Core i7 2600 $252.00
Intel Core i5 2400 $120.00
Intel Core i5 2400 $190.00
Intel Core i3 2120 NULL

Note the important NULL result that shows that although the left table has a table for 'Intel Core i3 2120', the right table has no rows that match. What this shows to the user of the SQL database is that there are no purchases for the 'Intel Core i3 2120'.

Conversley, this could work with a left join using the following statement:

SELECT `product_name`, `price` FROM `Product_Info` i LEFT JOIN `Purchases` p ON p.`product_id` = i.`product_id`

This would return the same results.

Feedback 👍
Comments are sent via email to me.