Unlock the Power of SQL RIGHT JOIN
What is a SQL RIGHT JOIN?
A SQL RIGHT JOIN returns records that have matching values in both tables, along with all the remaining rows from the right table. This means that even if there’s no match in the left table, the result set will still include all the rows from the right table.
The Anatomy of a RIGHT JOIN
The syntax of a SQL RIGHT JOIN is as follows:
SELECT column1, column2
FROM table1
RIGHT JOIN table2
ON table1.column1 = table2.column2;Here, table1 is the left table, table2 is the right table, and column1 and column2 are the related columns in both tables.
A Real-World Example
Let’s say we want to join the Customers and Orders tables based on the customer_id column. The result set will contain the customer_id and first_name columns from the Customers table, along with the item column from the Orders table, including those whose customer_id value is not present in the Customers table.
SELECT C.customer_id, C.first_name, O.item
FROM Customers C
RIGHT JOIN Orders O
ON C.customer_id = O.customer_id;Adding a WHERE Clause
The SQL RIGHT JOIN statement can also include an optional WHERE clause to filter the results. For instance, we can join the Customers and Orders tables and select rows where the amount is greater than or equal to 500.
SELECT C.customer_id, C.first_name, O.item
FROM Customers C
RIGHT JOIN Orders O
ON C.customer_id = O.customer_id
WHERE O.amount >= 500;Using AS Aliases
To make our SQL code more concise and readable, we can use AS aliases inside the RIGHT JOIN. For example, we can perform a right join on the Categories and Products tables while assigning the aliases C and P to them, respectively.
SELECT C.category_name, P.product_name
FROM Categories C
RIGHT JOIN Products P
ON C.category_id = P.category_id;