SQL - Right Join

SQL - RIGHT JOIN

A RIGHT JOIN (or RIGHT OUTER JOIN) is a type of outer join that returns all rows from the right table (table2), and the matched rows from the left table (table1). If a row from the right table has no matching row in the left table, the result will contain NULL values for all columns from the left table.

The SQL RIGHT JOIN

Visually, you can think of a RIGHT JOIN as taking every row from the right table and then trying to find a match in the left table based on the join condition.

Key takeaway: All rows from the right table are included in the result, regardless of whether they have a match in the left table.

Syntax

SELECT
    table1.column_name,
    table2.column_name
FROM
    table1
RIGHT JOIN table2 ON table1.common_field = table2.common_field;

Note: RIGHT JOIN and RIGHT OUTER JOIN are functionally identical in all major SQL dialects.

Example

Let’s consider two tables: CUSTOMERS and ORDERS.

CREATE TABLE CUSTOMERS (
    ID INT PRIMARY KEY,
    NAME VARCHAR(100) NOT NULL,
    AGE INT NOT NULL
);

INSERT INTO CUSTOMERS (ID, NAME, AGE) VALUES
(1, 'Ramesh', 32),
(2, 'Khilan', 25),
(3, 'Kaushik', 23),
(4, 'Chaitali', 25);

CREATE TABLE ORDERS (
    OID INT PRIMARY KEY,
    ORDER_DATE TIMESTAMP NOT NULL,
    CUSTOMER_ID INT,
    AMOUNT DECIMAL(10, 2)
);

INSERT INTO ORDERS (OID, ORDER_DATE, CUSTOMER_ID, AMOUNT) VALUES
(101, '2023-11-20 10:30:00', 2, 1560.00),
(102, '2023-10-08 14:00:00', 3, 3000.00),
(103, '2023-05-20 18:45:00', 4, 2060.00),
(104, '2023-12-01 09:00:00', 99, 500.00); -- Order from a non-existent customer

Notice the ORDERS table has an order with CUSTOMER_ID 99, which does not exist in the CUSTOMERS table. Let’s perform a RIGHT JOIN to list all orders and the customers who placed them.

SELECT
    c.ID,
    c.NAME,
    o.OID,
    o.AMOUNT
FROM
    CUSTOMERS c
RIGHT JOIN ORDERS o ON c.ID = o.CUSTOMER_ID;

Output

ID	NAME	OID	AMOUNT
2	Khilan	101	1560.00
3	Kaushik	102	3000.00
4	Chaitali	103	2060.00
NULL	NULL	104	500.00

The result includes all four orders. For Order ID 104, the customer columns (ID, NAME) are NULL because CUSTOMER_ID 99 has no match in the CUSTOMERS table.

Pro Tip: Prefer LEFT JOIN for Readability

While RIGHT JOIN is functionally valid, many developers find LEFT JOIN more intuitive and easier to read, as we tend to think from left to right. Any RIGHT JOIN can be rewritten as a LEFT JOIN by simply swapping the tables.

The previous RIGHT JOIN is equivalent to this LEFT JOIN:

-- This LEFT JOIN produces the exact same result and is often clearer.
SELECT
    c.ID,
    c.NAME,
    o.OID,
    o.AMOUNT
FROM
    ORDERS o
LEFT JOIN CUSTOMERS c ON o.CUSTOMER_ID = c.ID;

Joining Multiple Tables

You can chain multiple joins together. The joins are typically evaluated from left to right. Let’s add a SALESPERSON table and link it to ORDERS.

-- Add a SALESPERSON_ID to ORDERS and create a SALESPERSON table
ALTER TABLE ORDERS ADD COLUMN SALESPERSON_ID INT;
UPDATE ORDERS SET SALESPERSON_ID = 1 WHERE OID IN (101, 102);
UPDATE ORDERS SET SALESPERSON_ID = 2 WHERE OID IN (103, 104);

CREATE TABLE SALESPERSON (
    SID INT PRIMARY KEY,
    SNAME VARCHAR(100)
);
INSERT INTO SALESPERSON (SID, SNAME) VALUES (1, 'Sarika'), (2, 'Vivek');

-- Let's find all salespeople and the orders they are associated with
SELECT
    s.SNAME AS SalespersonName,
    o.OID AS OrderID,
    c.NAME AS CustomerName
FROM
    CUSTOMERS c
RIGHT JOIN ORDERS o ON c.ID = o.CUSTOMER_ID
RIGHT JOIN SALESPERSON s ON o.SALESPERSON_ID = s.SID;

Output

This query starts with SALESPERSON as the base (the rightmost table in the final join), ensuring all salespeople are listed.

SalespersonName	OrderID	CustomerName
Sarika	101	Khilan
Sarika	102	Kaushik
Vivek	103	Chaitali
Vivek	104	NULL

RIGHT JOIN with a WHERE Clause

The WHERE clause is used to filter the results after the join has been performed. This allows you to apply conditions to the joined result set.

SELECT
    c.ID, c.NAME, o.AMOUNT
FROM
    CUSTOMERS c
RIGHT JOIN ORDERS o ON c.ID = o.CUSTOMER_ID
WHERE
    o.AMOUNT > 2000.00;

Output

This will return only the orders with an amount greater than 2000.00, while still maintaining the RIGHT JOIN logic.

ID	NAME	AMOUNT
3	Kaushik	3000.00
4	Chaitali	2060.00