Lesson 3

Mastering LEFT JOIN and RIGHT JOIN in SQL

Introduction to Beyond the Hits

Congratulations! Having grasped the essentials and INNER JOINs with Taylor Swift's discography, it's time to delve deeper into SQL JOINs. Our next stops are the LEFT JOIN and RIGHT JOIN. Yet, before we tackle the more complex queries within Swift's rich dataset, let's clearly understand these joins through a simplified example. This step is crucial for laying a solid foundation.

Recap of SQL Joins

Before proceeding, remember that SQL JOINs allow us to combine data from two or more tables, based on a related column. We've previously explored INNER JOIN, which selects rows that have matching values in both tables. Now, we'll see how LEFT JOIN and RIGHT JOIN expand our data manipulation capabilities.

Sample Tables Overview

To elucidate the nuances of each JOIN type, consider two simple tables: Orders and Customers. The Orders table includes an OrderID, CustomerID, and OrderAmount. Importantly, the CustomerID column in the Orders table tracks which customer made each order and serves as the key link between the Orders and Customers tables. This key is crucial for executing JOIN operations between these tables.

Orders Table:

OrderIDCustomerIDOrderAmount
11100
22150
33200

Customers Table:

Here, each customer is identified by a unique CustomerID and a name. This table allows us to connect each order to the specific customer who placed it by matching CustomerID values in both tables.

CustomerIDName
1John Doe
2Jane Doe
4Jim Beam
INNER JOIN Explained

INNER JOIN fetches rows when there's at least one match in both tables. If there's no match, the rows aren't included in the output. In our example, OrderID 1 and 2 have corresponding customer details in the Customers table, hence they appear in the result.

Query:

SQL
1SELECT Orders.OrderID, Orders.OrderAmount, Customers.CustomerID, Customers.Name 2FROM Orders 3INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

Expected Result:

OrderIDOrderAmountCustomerIDName
11001John Doe
21502Jane Doe
Understanding the LEFT JOIN

LEFT JOIN brings all rows from the left table and the matched ones from the right. When there's no match, the output shows NULL for the right table's columns. In this case, OrderID 3 doesn't match any CustomerID from the Customers table, resulting in NULL values in the output.

Query:

SQL
1SELECT Orders.OrderID, Orders.OrderAmount, Customers.CustomerID, Customers.Name 2FROM Orders 3LEFT JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

Expected Result:

OrderIDOrderAmountCustomerIDName
11001John Doe
21502Jane Doe
3200NULLNULL
Diving into the RIGHT JOIN

In RIGHT JOIN, every row from the right table is included, pairing with the matched rows from the left table. NULL fills in for the non-matching left table. For instance, CustomerID 4 from Customers table has no matching order in the Orders table, shown by NULL values for OrderID and OrderAmount.

Query:

SQL
1SELECT Orders.OrderID, Orders.OrderAmount, Customers.CustomerID, Customers.Name 2FROM Orders 3RIGHT JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

Expected Result:

OrderIDOrderAmountCustomerIDName
11001John Doe
21502Jane Doe
NULLNULL4Jim Beam
Clarity on NULL

In this examples, NULL signifies the absence of a value or a non-existent match between our JOIN criteria. It's essential for highlighting unmatched data between interconnected tables, playing a critical role in LEFT JOIN and RIGHT JOIN. More than just an indicator for no data, NULL helps maintain the integrity of the dataset by showing where data relationships do not exist, without dropping entire rows from the result.

Summary and Next Steps

This exploration has highlighted the distinct functions of INNER JOIN, LEFT JOIN, and RIGHT JOIN:

  • INNER JOIN is the picky diner, only wanting rows that meet in both tables.
  • LEFT JOIN is the accommodating friend, keeping all from the left table, regardless of a match.
  • RIGHT JOIN mirrors LEFT JOIN but favors the right table, keeping all its rows.

Now equipped with a clearer understanding of each JOIN type, we're ready to tackle more intricate queries involving Swift's rich database in the upcoming practices. Anticipate diving into FULL JOIN in our upcoming lesson, where we continue unraveling the complexities of SQL JOINs. Onward in your SQL journey!

Enjoy this lesson? Now it's time to practice with Cosmo!

Practice is how you turn knowledge into actual skills.