There are two other kinds of join in SQL, left and right.
Left and Right Joins in SQL
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:
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.
