Great work! You've grasped the essentials and have now explored INNER JOIN in the context of an online shopping platform. Now it's time to delve deeper into SQL JOINs using data related to customer orders and order items. We will focus on LEFT JOIN and RIGHT JOIN. Before we start working with the more detailed order data at our disposal, it's vital for us to clearly understand these joins through a simpler example.

Before we start, keep in mind that SQL JOINs allow us to combine data from two or more tables based on a related column. Previously, we've worked with INNER JOIN, which selects rows that have matching values in both tables. In this lesson, we'll explore how LEFT JOIN and RIGHT JOIN can help us manipulate our data further.

To understand these JOIN types better, consider two simple tables: Orders and OrderItems.

Orders Table:

The Orders table provides detailed information about each order, including the order_id, date, status, and the customer who placed the order. This is useful for tracking order specifics.

OrderItems Table:

The OrderItems table connects each order to the specific items within it using corresponding order_id values.

INNER JOIN returns rows when there's a match in both tables. If there's no match, those rows are not included in the output.

Example:

SQL
Copy to clipboard
1SELECT Orders.order_date, Orders.order_status, OrderItems.product_id
2FROM Orders
3INNER JOIN OrderItems ON Orders.order_id = OrderItems.order_id;
4
5-- Sneak peek of the output:
6-- | order_date | order_status | product_id |
7-- |------------|--------------|------------|
8-- | 2021-08-17 | Delivered    | 25         |
9-- | 2022-04-03 | Processed    | 12         |

LEFT JOIN includes all rows from the left table, along with any matches from the right table. If there's no match, the output displays NULL for the right table's columns.

Example:

SQL
Copy to clipboard
1SELECT Orders.order_date, Orders.order_status, OrderItems.product_id
2FROM Orders
3LEFT JOIN OrderItems ON Orders.order_id = OrderItems.order_id;
4
5-- Sneak peek of the output:
6-- | order_date | order_status | product_id |
7-- |------------|--------------|------------|
8-- | 2021-08-17 | Delivered    | 25         |
9-- | 2022-04-03 | Processed    | 12         |

RIGHT JOIN ensures that every row from the right table is included in the output, with matched rows from the left table.

Example:

SQL
Copy to clipboard
1SELECT Orders.order_date, Orders.order_status, OrderItems.product_id
2FROM Orders
3RIGHT JOIN OrderItems ON Orders.order_id = OrderItems.order_id;
4
5-- Sneak peek of the output:
6-- | order_date | order_status | product_id |
7-- |------------|--------------|------------|
8-- | 2021-08-17 | Delivered    | 25         |
9-- | 2022-04-03 | Processed    | 12         |

Note: With the provided sample tables, there currently seems to be no visible difference due to the limited number of rows and the fact that each order has a corresponding order item. However, generally, there is a difference: RIGHT JOIN includes all rows from the right table (OrderItems) and any corresponding rows from the left table (Orders). It's advisable to try this on tables with more rows and different cases where some rows in the right table do not have corresponding rows in the left table to see the full effect.

To better highlight the distinctions between LEFT JOIN and RIGHT JOIN, let's expand the sample tables:

Orders Table:

OrderItems Table:

LEFT JOIN Example:

SQL
Copy to clipboard
1SELECT Orders.order_id, Orders.order_date, OrderItems.product_id
2FROM Orders
3LEFT JOIN OrderItems ON Orders.order_id = OrderItems.order_id;
4
5-- Result:
6-- | order_id | order_date | product_id |
7-- |----------|------------|------------|
8-- | 1        | 2021-08-17 | 25         |
9-- | 2        | 2022-04-03 | 12         |
10-- | 3        | NULL       | NULL       |

RIGHT JOIN Example:

SQL
Copy to clipboard
1SELECT Orders.order_id, Orders.order_date, OrderItems.product_id
2FROM Orders
3RIGHT JOIN OrderItems ON Orders.order_id = OrderItems.order_id;
4
5-- Result:
6-- | order_id | order_date | product_id |
7-- |----------|------------|------------|
8-- | 1        | 2021-08-17 | 25         |
9-- | 2        | 2022-04-03 | 12         |
10-- | NULL     | NULL       | 18         |

Key Difference:

• LEFT JOIN includes all rows from Orders, showing NULL for OrderItems columns when there’s no match.
• RIGHT JOIN includes all rows from OrderItems, showing NULL for Orders columns when there’s no match.

Having explored INNER JOIN, LEFT JOIN, and RIGHT JOIN in detail:

• INNER JOIN returns rows with matching values in both tables.
• LEFT JOIN holds all data from the left table, irrespective of whether there's a match in the right.
• RIGHT JOIN behaves similarly to LEFT JOIN but favors the right table.

With a solid understanding of each JOIN type, we are now ready to explore even more complex queries using the order and order item datasets in upcoming lessons. Prepare to delve into FULL JOIN in our next lesson, as we continue unraveling the subtleties of SQL JOINs.