RIGHT JOIN execution behavior in SQL

Introduction

A RIGHT JOIN helps you combine two tables and keep all rows from the table on the right, even if there is no matching data on the left.

You want to see all customers and their orders, including customers who have not placed any orders.

You have a list of employees and their departments, and you want to show all departments even if some have no employees.

You want to compare two lists and keep all items from the second list, showing related data from the first if available.

Syntax

SQL

SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.common_column = table2.common_column;

The RIGHT JOIN returns all rows from the right table (table2), and matching rows from the left table (table1).

If there is no match, columns from the left table will have NULL values.

Examples

Shows all departments and any employees in them. Departments without employees still appear.

SQL

SELECT *
FROM Employees
RIGHT JOIN Departments
ON Employees.DepartmentID = Departments.ID;

Lists all customers and their orders. Customers without orders still appear with NULL order info.

SQL

SELECT Orders.OrderID, Customers.Name
FROM Orders
RIGHT JOIN Customers
ON Orders.CustomerID = Customers.ID;

Sample Program

This query shows all customers and their orders. Bob has no orders, so his order info is NULL.

SQL

CREATE TABLE Customers (ID INT, Name VARCHAR(20));
CREATE TABLE Orders (OrderID INT, CustomerID INT);

INSERT INTO Customers VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Carol');
INSERT INTO Orders VALUES (101, 1), (102, 1), (103, 3);

SELECT Customers.ID, Customers.Name, Orders.OrderID
FROM Orders
RIGHT JOIN Customers
ON Orders.CustomerID = Customers.ID
ORDER BY Customers.ID;

Important Notes

RIGHT JOIN is like LEFT JOIN but keeps all rows from the right table instead of the left.

If you switch the table order, you can use LEFT JOIN to get the same result as RIGHT JOIN.

NULL values appear for columns from the table that has no matching row.

Summary

RIGHT JOIN keeps all rows from the right table and matches rows from the left table.

Unmatched rows from the right table show NULL values.

Useful to ensure no data is lost from the right table when combining tables.