Understanding Advanced SQL Joins with Online Shop's Orders Data

Introduction to Diving Deeper into Joins

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.

Recap of SQL Joins

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.

Sample Tables Overview

To understand these JOIN types better, consider two simple tables: Orders and OrderItems . Orders Table: order_id customer_id order_date order_status 141 2021-08-17 Delivered 216 2022-04-03 Processed 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: order_item_id order_id product_id extended_support 1125022120 The OrderItems table connects each order to the specific items within it using corresponding order_id values.

INNER JOIN Explained

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 SELECT Orders.order_date, Orders.order_status, OrderItems.product_id FROM Orders INNER JOIN OrderItems ON Orders.order_id = OrderItems.order_id; -- Sneak peek of the output: -- | order_date | order_status | product_id | -- |------------|--------------|------------| -- | 2021-08-17 | Delivered | 25 | -- | 2022-04-03 | Processed | 12 |

Join the 1M+ learners on CodeSignal

Be a part of our community of 1M+ users who develop and demonstrate their skills on CodeSignal

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

SELECT Orders.order_date, Orders.order_status, OrderItems.product_id
FROM Orders
LEFT JOIN OrderItems ON Orders.order_id = OrderItems.order_id;

-- Sneak peek of the output:
-- | order_date | order_status | product_id |
-- |------------|--------------|------------|
-- | 2021-08-17 | Delivered    | 25         |
-- | 2022-04-03 | Processed    | 12         |

SELECT Orders.order_date, Orders.order_status, OrderItems.product_id
FROM Orders
LEFT JOIN OrderItems ON Orders.order_id = OrderItems.order_id;

-- Sneak peek of the output:
-- | order_date | order_status | product_id |
-- |------------|--------------|------------|
-- | 2021-08-17 | Delivered    | 25         |
-- | 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

SELECT Orders.order_date, Orders.order_status, OrderItems.product_id
FROM Orders
RIGHT JOIN OrderItems ON Orders.order_id = OrderItems.order_id;

-- Sneak peek of the output:
-- | order_date | order_status | product_id |
-- |------------|--------------|------------|
-- | 2021-08-17 | Delivered    | 25         |
-- | 2022-04-03 | Processed    | 12         |

SELECT Orders.order_date, Orders.order_status, OrderItems.product_id
FROM Orders
RIGHT JOIN OrderItems ON Orders.order_id = OrderItems.order_id;

-- Sneak peek of the output:
-- | order_date | order_status | product_id |
-- |------------|--------------|------------|
-- | 2021-08-17 | Delivered    | 25         |
-- | 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.

Understanding Differences Between LEFT JOIN and RIGHT JOIN

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

Orders Table:

order_id	customer_id	order_date	order_status
1	41	2021-08-17	Delivered
2	16	2022-04-03	Processed
3	12	NULL	Canceled

OrderItems Table:

order_item_id	order_id	product_id	extended_support
1	1	25	0
2	2	12	0
3	4	18	1

LEFT JOIN Example:

SQL

SELECT Orders.order_id, Orders.order_date, OrderItems.product_id
FROM Orders
LEFT JOIN OrderItems ON Orders.order_id = OrderItems.order_id;

-- Result:
-- | order_id | order_date | product_id |
-- |----------|------------|------------|
-- | 1        | 2021-08-17 | 25         |
-- | 2        | 2022-04-03 | 12         |
-- | 3        | NULL       | NULL       |

SELECT Orders.order_id, Orders.order_date, OrderItems.product_id
FROM Orders
LEFT JOIN OrderItems ON Orders.order_id = OrderItems.order_id;

-- Result:
-- | order_id | order_date | product_id |
-- |----------|------------|------------|
-- | 1        | 2021-08-17 | 25         |
-- | 2        | 2022-04-03 | 12         |
-- | 3        | NULL       | NULL       |

RIGHT JOIN Example:

SQL

SELECT Orders.order_id, Orders.order_date, OrderItems.product_id
FROM Orders
RIGHT JOIN OrderItems ON Orders.order_id = OrderItems.order_id;

-- Result:
-- | order_id | order_date | product_id |
-- |----------|------------|------------|
-- | 1        | 2021-08-17 | 25         |
-- | 2        | 2022-04-03 | 12         |
-- | NULL     | NULL       | 18         |

SELECT Orders.order_id, Orders.order_date, OrderItems.product_id
FROM Orders
RIGHT JOIN OrderItems ON Orders.order_id = OrderItems.order_id;

-- Result:
-- | order_id | order_date | product_id |
-- |----------|------------|------------|
-- | 1        | 2021-08-17 | 25         |
-- | 2        | 2022-04-03 | 12         |
-- | 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.

Introduction to Diving Deeper into Joins

Recap of SQL Joins

Sample Tables Overview

INNER JOIN Explained

Join the 1M+ learners on CodeSignal

Understanding the LEFT JOIN

Diving into the RIGHT JOIN

Understanding Differences Between LEFT JOIN and RIGHT JOIN

Summary and Next Steps