Mastering LEFT JOIN and RIGHT JOIN in SQL

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.

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.

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:

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.

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:

SELECT Orders.OrderID, Orders.OrderAmount, Customers.CustomerID, Customers.Name
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

Expected Result: