SQL RIGHT JOIN

The SQL Server RIGHT OUTER JOIN combines two or more tables and returns all the records present in the right (second) table and matches rows from the left (first) table. Even if there is no matching record on the first table, the RIGHT JOIN will return all rows from the second table. However, all the unmatched rows from the first table will be filled with NULL Values.

Let us see the visual representation, or Venn diagram, of the SQL Server RIGHT JOIN for a better understanding. From the above image, you can easily understand that it displays all the records present in Table2 and matching records in Table1.

RIGHT OUTER JOIN Ven Diagram

SQL RIGHT JOIN Syntax

The syntax of the RIGHT JOIN to establish a connection between two or more tables is as follows:

SELECT t1.Column(s), t2.Column(s),
FROM t1
RIGHT OUTER JOIN t2 
ON t1.Common_Column = t2.Common_Column

--OR We can Simply Write it as
SELECT t1. Column(s), t2. Column(s),
FROM t1
RIGHT JOIN t2 
ON t1.Common_Column = t2.Common_Column

TIP: As you can notice from the above two syntaxes, both SQL RIGHT OUTER and RIGHT JOIN are the same and return the same output. So, it is optional to use the OUTER keyword.

In the above RIGHT JOIN syntax:

  • t1 is the left (first) table.
  • t2 is the right (second) table.
  • Common_Column is the common or related column in the two tables to establish a connection between t1 and t2.
  • Use the SELECT statement to select the required columns from both tables.

To demonstrate the SQL Server RIGHT OUTER JOIN example, we will create three different tables: countries, customers, and their orders. You can use the SQL Server script to create them in your work environment.

CREATE TABLE Countries (
    CountryID INT PRIMARY KEY,
    Country VARCHAR(100) NOT NULL
);
INSERT INTO Countries (CountryID, Country) VALUES
(1, 'USA'),
(2, 'INDIA'),
(3, 'UK');

CREATE TABLE customers (
    CustomerID INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    CountryID INT FOREIGN KEY REFERENCES Countries(CountryID)
);
INSERT INTO customers (CustomerID, Name, CountryID) VALUES
(1, 'John Smith', 1),
(2, 'Ram Narayan', 2),
(3, 'Tracy White', 2),
(4, 'David Williams', 1);

CREATE TABLE orders (
    CustomerID INT FOREIGN KEY REFERENCES customers(CustomerID),
    OrderDate DATE NOT NULL,
    Quantity INT NULL,
    Amount DECIMAL(10, 2) NULL
);

INSERT INTO Orders (CustomerID, OrderDate, Quantity, Amount) VALUES
(1, '2025-01-01', 2, 2500.00),
(NULL, '2025-02-14', 3, 4000.00),
(2, '2025-03-20', 1, 1500.00),
(3, '2025-04-11', 5, 6000.00),
(4, '2025-06-01', 7, 8000.00),
(1, '2025-08-15', 10, 10000.00),
(2, '2025-10-12', 4, 5000.00),
(NULL, '2025-09-20', 2, 2000.00);

SQL RIGHT JOIN Example

We start the series of examples with a simple one where we use (*) to display all columns from both tables. Suppose you want to see the total number of orders irrespective of the associated customers (may or may not). In such a case, use RIGHT JOIN to prioritise the orders table and linked customers (if any).

The following RIGHT JOIN query displays all columns in the orders table and matching records from the customers table.

SELECT * FROM [customers]
RIGHT JOIN [orders]
ON customers.CustomerID = orders.CustomerID

NOTE: The SQL RIGHT OUTER JOIN returns all the rows in the orders table, even if there are no matching records from the customers table.

How the query works

  • The RIGHT JOIN will combine the customers and orders tables from a database.
  • The ON clause uses the common CustomerID column to establish a connection between the two tables.
  • SELECT * FROM will choose all columns from both tables.
  • As it is the RIGHT JOIN, every row present in the orders (second) table is displayed in the result set, and the linked or matching customers are also displayed. If there is no match, the server autofills those cells with NULL values.

Optional OUTER Keyword

As we mentioned earlier, it is optional to use an OUTER keyword. So, remove the OUTER keyword from the above example, and check the SQL Server RIGHT JOIN query result.

SELECT * FROM [customers]
RIGHT OUTER JOIN [orders]
ON customers.CustomerID = orders.CustomerID
CustomerID	Name	CountryID	CustomerID	OrderDate	Quantity	Amount
1	John Smith	1	1	2025-01-01	2	2500.00
NULL	NULL	NULL	NULL	2025-02-14	3	4000.00
2	Ram Narayan	2	2	2025-03-20	1	1500.00
3	Tracy White	2	3	2025-04-11	5	6000.00
4	David Williams	1	4	2025-06-01	7	8000.00
1	John Smith	1	1	2025-08-15	10	10000.00
2	Ram Narayan	2	2	2025-10-12	4	5000.00
NULL	NULL	NULL	NULL	2025-09-20	2	2000.00

As we expected, it returns the same result as the first example. Let’s examine the result set to understand the records.

  • As there are eight records in the orders table, it displays all of them.
  • If you observe the second and the eighth record, the first four columns of those rows display NULL values. Because the CustomerID for those records in the orders table are NULL, there are no matching records for them in the customers table. So they are replaced by NULLS.

Select required columns

If you take a closer look at the SQL RIGHT JOIN result set, the CustomerID column is repeated twice and an unwanted CountryID column. It may be OK to see all the columns at a glance, but it will annoy the end-user. By selecting individual column names, we can avoid unwanted columns. So, please avoid SELECT * Statements in the RIGHT JOIN and choose the required columns after the SELECT Statement to avoid unwanted columns in JOINS.

SELECT Name, OrderDate, Quantity, Amount
FROM [customers]
RIGHT JOIN [orders]
ON customers.CustomerID = orders.CustomerID
Name	OrderDate	Quantity	Amount
John Smith	2025-01-01	2	2500.00
NULL	2025-02-14	3	4000.00
Ram Narayan	2025-03-20	1	1500.00
Tracy White	2025-04-11	5	6000.00
David Williams	2025-06-01	7	8000.00
John Smith	2025-08-15	10	10000.00
Ram Narayan	2025-10-12	4	5000.00
NULL	2025-09-20	2	2000.00

SQL RIGHT JOIN Ambiguous column Error: Fix

The above-mentioned RIGHT JOIN query will work exceptionally as long as the column names from both the customers and orders tables are different, like above. If we choose the same Column name that is present in both tables, we get the Ambiguous column error. Let us see how to solve the issue.

Here, we used the above RIGHT OUTER JOIN query. However, we added the CustomerID in the SELECT statement as an additional column.

SELECT CustomerID, Name, OrderDate, Quantity, Amount
FROM [customers]
RIGHT JOIN [orders]
ON customers.CustomerID = orders.CustomerID

As you see the Message, the SQL RIGHT JOIN throws an error: Ambiguous column name ‘CustomerID’. It is because the ‘CustomerID’ column is available in both the customers and orders tables. And the server doesn’t recognise which column you are claiming.

Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'CustomerID'.

To resolve this kind of concern, practice the table name before the column name. For example, the following RIGHT OUTER JOIN query uses the ALIAS table name before the column names.

By this approach, we can notify the SQL Server that we are looking for the ‘CustomerID’ column belonging to the customers table.

SELECT c.CustomerID, c.Name, o.OrderDate, o.Quantity, o.Amount
FROM customers c
RIGHT JOIN orders o
ON c.CustomerID = o.CustomerID

Order of the Table Matters

Whether you are working with LEFT or RIGHT JOIN, the order of the tables placed plays a major role. As the SQL RIGHT OUTER JOIN selects all rows from the second table, if we change the order of the customers and orders tables, it produces completely different results.

The query below produces a total of six records. There are four customers in the right table, and there are multiple (2) orders for CustomerIDs 1 and 2. So, a total of six records are displayed. Although there are two more rows in the order tables with CustomerID NULL, those two are ignored from the result set because there is no matching record in the customers table, which is the actual priority table.

SELECT c.CustomerID, c.Name,o.OrderDate, o.Quantity, o.Amount
FROM orders o
RIGHT JOIN customers c
ON c.CustomerID = o.CustomerID
SQL Server RIGHT JOIN Example

SQL RIGHT JOIN on Multiple Tables

Like any other JOIN, the RIGHT OUTER JOIN supports combining more than two tables. In a normalised database, it is common to store similar data in multiple tables. So, in most cases, we may use the RIGHT JOIN to combine three or more tables to get the required results.

NOTE: When joining three tables, the RIGHT JOIN considers the third table as the right table and returns all the records in the third table, along with the relevant records from the result set of the first join.

The query below returns a total of seven records. Let me explain the result set in step-by-step.

  • From the above examples, you may notice that the result set of the first RIGHT JOIN between the customers and orders return eight records. As there are 8 rows in orders, those will return.
  • The second SQL RIGHT OUTER JOIN considers the countries table as the second table and 8 rows (first result set) as the first table. The countries table has three records, and there are no customers from the UK region. So, for the UK region, it fills the remaining columns with NULL values. Next, it returns six records that have the CustomerID in the first result set.
SELECT c.CustomerID, c.Name,g.Country, o.OrderDate, o.Quantity, o.Amount
FROM customers c
RIGHT JOIN orders o
ON c.CustomerID = o.CustomerID
RIGHT JOIN countries g
ON c.CountryID = g.CountryID
ORDER BY c.CustomerID

If you change the second RIGHT JOIN to LEFT, it returns all eight records from the result set and the matching ones from the countries table.

SELECT c.CustomerID, c.Name,g.Country, o.OrderDate, o.Quantity, o.Amount
FROM customers c
RIGHT JOIN orders o
ON c.CustomerID = o.CustomerID
LEFT JOIN countries g
ON c.CountryID = g.CountryID
ORDER BY c.CustomerID
CustomerID	Name	Country	OrderDate	Quantity	Amount
NULL	NULL	NULL	2025-02-14	3	4000.00
NULL	NULL	NULL	2025-09-20	2	2000.00
1	John Smith	USA	2025-01-01	2	2500.00
1	John Smith	USA	2025-08-15	10	10000.00
2	Ram Narayan	INDIA	2025-10-12	4	5000.00
2	Ram Narayan	INDIA	2025-03-20	1	1500.00
3	Tracy White	INDIA	2025-04-11	5	6000.00
4	David Williams	USA	2025-06-01	7	8000.00

SQL RIGHT JOIN with WHERE Clause

The RIGHT JOIN also allows us to use the WHERE clause to restrict the records returned. As we combine two large tables, the query result set may return a large dataset, and a few rows or columns may not be required. In such a case, you can use the WHRE clause along with the Right Outer Join.

The following query performs the RIGHT JOIN in the customers and order tables and displays the customer who ordered between March 2025 and 1st September.

SELECT c.CustomerID, c.Name, o.OrderDate, o.Quantity, o.Amount
FROM customers c
RIGHT JOIN orders o
ON c.CustomerID = o.CustomerID
WHERE OrderDate BETWEEN '2025-03-01' AND '2025-09-01'
ORDER BY c.CustomerID
CustomerID	Name	OrderDate	Quantity	Amount
1	John Smith	2025-08-15	10	10000.00
2	Ram Narayan	2025-03-20	1	1500.00
3	Tracy White	2025-04-11	5	6000.00
4	David Williams	2025-06-01	7	8000.00

We can also use the WHERE clause and IS NOT NULL operator to filter or exclude the NULL records from the RIGHT JOIN result set.

SELECT g.Country, COUNT(c.CustomerID) TotalCustomers,
	SUM(o.Quantity) Quantity, SUM(o.Amount) Total
FROM customers c
RIGHT JOIN countries g
ON c.CountryID = g.CountryID
RIGHT JOIN orders o
ON c.CustomerID = o.CustomerID
WHERE o.CustomerID IS NOT NULL
GROUP BY g.Country
Country	TotalCustomers	Quantity	Total
INDIA	3	10	12500.00
USA	3	19	20500.00

SQL RIGHT JOIN with GROUP BY and ORDER BY

When performing the RIGHT JOIN to combine two or more tables, use the aggregate function, such as the SUM, COUNT, AVG, etc, to perform the complex calculation. Suppose you want to calculate the total number of orders placed by each customer, the number of items purchased, the total sale and its average. In such a case, use the RIGHT JOIN to combine the two tables and an aggregate function to calculate those values.

SELECT c.Name, COUNT(c.CustomerID) TotalOrders,
	SUM(o.Quantity) Quantity, 
	SUM(o.Amount) Total, AVG(o.Amount) AS Average
FROM customers c
RIGHT JOIN orders o
ON c.CustomerID = o.CustomerID
GROUP BY c.Name
Name	TotalOrders	Quantity	Total	Average
NULL	0	5	6000.00	3000.000000
David Williams	1	7	8000.00	8000.000000
John Smith	2	12	12500.00	6250.000000
Ram Narayan	2	5	6500.00	3250.000000
Tracy White	1	5	6000.00	6000.000000

Similar to the above, you can use the SUM and AVG aggregations when using the RIGHT JOIN on multiple tables. Here, we used the combination of LEFT and RIGHT JOINs to display the countries and their total sales information.

SELECT g.Country, COUNT(c.CustomerID) TotalCustomers,
	SUM(o.Quantity) Quantity, 
	SUM(o.Amount) Total, AVG(o.Amount) AS Average
FROM customers c
RIGHT JOIN countries g
ON c.CountryID = g.CountryID
LEFT JOIN orders o
ON c.CustomerID = o.CustomerID
--WHERE o.CustomerID IS NOT NULL
GROUP BY g.Country
Country	TotalCustomers	Quantity	Total	Average
INDIA	3	10	12500.00	4166.666666
UK	0	NULL	NULL	NULL
USA	3	19	20500.00	6833.333333

NOTE: If you uncomment the WHERE clause, it returns only two rows because of the IS NOT NULL operator.

ORDER BY

If you use the RIGHT JOIN with the combination of the ORDER BY Clause, it can rearrange the order of the records. For instance, the query below sorts the result set based on the total sales amount in descending order.

SELECT c.Name, COUNT(c.CustomerID) TotalOrders,
	SUM(o.Quantity) Quantity, 
	SUM(o.Amount) Total, AVG(o.Amount) AS Average
FROM customers c
RIGHT JOIN orders o
ON c.CustomerID = o.CustomerID
GROUP BY c.Name
ORDER BY Total DESC

Handle null values in SQL RIGHT JOIN

As you may have understood by now, the RIGHT JOIN fills the unmatched (unlinked) rows in the left table with NULL values. Please use the COALESCE function to handle the NULLs and assign a default value. The second option is the WHERE clause to filter them out from the result set (refer to the above example and uncomment WHERE).

In the below query, COALESCE(g.Country, ‘Unknown’)  replaces the NULL country name with Unknown.

SELECT COALESCE(g.Country, 'Unknown') AS Country,
    COUNT(DISTINCT c.CustomerID) AS DistinctCustomers,
    COUNT(c.CustomerID) AS TotalCustomers,
    SUM(o.Quantity) AS Quantity,
    SUM(o.Amount) AS TotalSales,
    ROUND(AVG(o.Amount), 2) AS AvgValue
FROM countries g
LEFT JOIN Customers c
    ON g.CountryID = c.CountryID
RIGHT JOIN orders o
    ON c.CustomerID = o.CustomerID
--WHERE o.OrderDate >= '2025-03-01'
GROUP BY COALESCE(g.Country, 'Unknown')
ORDER BY Country;
SQL RIGHT JOIN with GROUP BY and ORDER BY
Categories SQL