To use this website fully, you first need to accept the use of cookies. By agreeing to the use of cookies you consent to the use of functional cookies. For more information read this page.

Left 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_Info

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

Purchases

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:

SQL
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:

SQL
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.

Provide feedback on this page
Comments are sent via email to me.