Left Outer Join vs Right Outer Join in SQL Server (Tested)

Picking between LEFT and RIGHT for a report query? The whole left outer join vs right outer join SQL Server question comes down to which table keeps all its rows. Swap the table order and both return identical results.

So use LEFT JOIN with the must-keep table first. That’s the convention.

Left Outer Join vs Right Outer Join at a Glance

Feature	LEFT [OUTER] JOIN	RIGHT [OUTER] JOIN
Keeps every row from	The table before the keyword	The table after it
Fills NULLs into	The right table’s columns	The left table’s columns
Plan operator (my run)	Nested Loops (Left Outer Join)	Same operator, inputs flipped

Test Setup for the LEFT and RIGHT JOIN Runs

Tested on SQL Server 2025 (17.0.1000.7) Developer, sqlcmd in VS Code. Tiny data on purpose. Order 105 is a guest checkout with no customer:

CREATE TABLE dbo.Customers (
    CustomerID int PRIMARY KEY,
    Name nvarchar(14), State char(2));
CREATE TABLE dbo.Orders (
    OrderID int PRIMARY KEY,
    CustomerID int NULL, Amount decimal(7,2));
CREATE TABLE dbo.Reps (Rep nvarchar(12), State char(2));
INSERT dbo.Customers VALUES (1, N'Hannah Brooks', 'TX'),
  (2, N'Tyler Reed', 'OH'), (3, N'Grace Miller', 'CA'),
  (4, N'Marcus Bennett', 'FL'), (5, N'Olivia Hayes', 'NY');
INSERT dbo.Orders VALUES (101, 1, 120.00), (102, 1, 45.50),
  (103, 3, 89.99), (104, 4, 230.00), (105, NULL, 15.00);
INSERT dbo.Reps VALUES (N'Ava Collins', 'TX'),
  (N'Noah Price', 'CA'), (N'Lily Ward', 'WA');

Same Rows Both Ways: LEFT JOIN vs Swapped RIGHT JOIN

Customers LEFT JOIN Orders, then the same tables with Orders first and a RIGHT JOIN:

SET NOCOUNT ON;
SELECT c.Name, o.OrderID
FROM dbo.Customers AS c
LEFT JOIN dbo.Orders AS o ON o.CustomerID = c.CustomerID
ORDER BY c.Name;

SELECT c.Name, o.OrderID
FROM dbo.Orders AS o
RIGHT JOIN dbo.Customers AS c ON o.CustomerID = c.CustomerID
ORDER BY c.Name;

Output:

Name           OrderID    
-------------- -----------
Grace Miller           103
Hannah Brooks          101
Hannah Brooks          102
Marcus Bennett         104
Olivia Hayes          NULL
Tyler Reed            NULL
Name           OrderID    
-------------- -----------
Grace Miller           103
Hannah Brooks          101
Hannah Brooks          102
Marcus Bennett         104
Olivia Hayes          NULL
Tyler Reed            NULL

What this tells me: identical rows. Customers is preserved in both, which is all a SQL Server LEFT JOIN promises.

Same Table Order, RIGHT JOIN Instead of LEFT: What Changes?

Same tables, Customers still first, but RIGHT JOIN:

SET NOCOUNT ON;
SELECT c.Name, o.OrderID
FROM dbo.Customers AS c
RIGHT JOIN dbo.Orders AS o ON o.CustomerID = c.CustomerID
ORDER BY o.OrderID;

Output:

Name           OrderID    
-------------- -----------
Hannah Brooks          101
Hannah Brooks          102
Grace Miller           103
Marcus Bennett         104
NULL                   105

Now Orders keeps every row. Guest order 105 gets a NULL name, and the customers without orders vanish, since the preserved and NULL-filled sides traded places. More in RIGHT JOIN in SQL Server.

Does SQL Server Treat a RIGHT JOIN as a Flipped LEFT JOIN?

I asked for the estimated plan of the swapped RIGHT JOIN:

SET SHOWPLAN_TEXT ON;
GO
SELECT c.Name, o.OrderID
FROM dbo.Orders AS o
RIGHT JOIN dbo.Customers AS c ON o.CustomerID = c.CustomerID;
GO
SET SHOWPLAN_TEXT OFF;

Plan text:

StmtText                                                                                                        
----------------------------------------------------------------------------------------------------------------
SELECT c.Name, o.OrderID
FROM dbo.Orders AS o
RIGHT JOIN dbo.Customers AS c ON o.CustomerID = c.CustomerID;   

(1 rows affected)
StmtText                                                                                                                                                                               
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  |--Nested Loops(Left Outer Join, WHERE:([RightLeftJoinDemo].[dbo].[Orders].[CustomerID] as [o].[CustomerID]=[RightLeftJoinDemo].[dbo].[Customers].[CustomerID] as [c].[CustomerID])) 
       |--Clustered Index Scan(OBJECT:([RightLeftJoinDemo].[dbo].[Customers].[PK__Customer__A4AE64B8165F6768] AS [c]))                                                                 
       |--Clustered Index Scan(OBJECT:([RightLeftJoinDemo].[dbo].[Orders].[PK__Orders__C3905BAF24A49A0E] AS [o]))                                                                      

(3 rows affected)

No Right Outer Join operator. The optimizer used Nested Loops (Left Outer Join) with Customers as the outer input, which is exactly the operator list my LEFT JOIN version got, so don’t expect a speed difference.

ON vs WHERE: When a Left Outer Join Turns Into an Inner Join

Same customers, counted three ways:

SET NOCOUNT ON;
SELECT
 (SELECT COUNT(*) FROM dbo.Customers c
  LEFT JOIN dbo.Orders o ON o.CustomerID = c.CustomerID
   AND o.Amount > 100) AS FilterInOn,
 (SELECT COUNT(*) FROM dbo.Customers c
  LEFT JOIN dbo.Orders o ON o.CustomerID = c.CustomerID
  WHERE o.Amount > 100) AS FilterInWhere,
 (SELECT COUNT(*) FROM dbo.Customers c
  LEFT JOIN dbo.Orders o ON o.CustomerID = c.CustomerID
  RIGHT JOIN dbo.Reps r ON r.State = c.State) AS LeftThenRight;

Output:

FilterInOn  FilterInWhere LeftThenRight
----------- ------------- -------------
          5             2             4

ON kept all 5 customers. WHERE kept 2: NULL > 100 isn’t true, so unmatched rows die after the join. See LEFT OUTER JOIN with a WHERE clause.

Important: the mixed chain returned 4 because the last RIGHT JOIN preserves Reps, not Customers. Keep every join LEFT and you never read a chain backward. More on outer joins in SQL Server.

Finding Unmatched Rows With a RIGHT or LEFT JOIN and IS NULL

Test the key of the NULL-filled table, from either side:

SET NOCOUNT ON;
SELECT c.Name AS NoOrders
FROM dbo.Orders AS o
RIGHT JOIN dbo.Customers AS c ON o.CustomerID = c.CustomerID
WHERE o.OrderID IS NULL;

Customers with no orders:

NoOrders      
--------------
Tyler Reed    
Olivia Hayes

The LEFT form is the same query with the tables swapped. For orphans on both sides, use a FULL OUTER JOIN with a WHERE clause.

When to Use RIGHT JOIN Instead of LEFT JOIN

• Default: LEFT JOIN.
• A long chain of inner joins needs every row of one more lookup table: a final RIGHT JOIN keeps it without reordering FROM.

To switch RIGHT to LEFT, swap the two table names and the keyword; ON stays the same.

My Takeaways on LEFT vs RIGHT JOIN

1. Swapped tables, same rows, same plan operator.
2. Filters on the NULL-filled table belong in ON.
3. Don’t mix LEFT and RIGHT in one chain.

I only tested five-row tables, so on a big table the optimizer can pick a hash or merge join instead of nested loops. The rows won’t change.

Next step: INNER JOIN vs LEFT JOIN. On Postgres: PostgreSQL joins.

• MariaDB version: MariaDB LEFT JOIN

Frequently Asked Questions