What Is the Point of the SQL RIGHT OUTER JOIN?
Structured Query Language (SQL) provides a range of tools to help us work with data stored in relational databases. One of the most powerful features in SQL is the ability to join data from multiple tables. SQL joins are fundamental to combining and analyzing related data spread across different tables in a relational schema.
Among the various types of joins, one often raises questions: the RIGHT OUTER JOIN. While it’s officially part of the SQL standard, many developers (especially beginners or those transitioning from other languages or platforms) may wonder:
“What is the purpose of the RIGHT OUTER JOIN when I can just switch the table order and use a LEFT OUTER JOIN instead?”
Learn SQL online
In this in-depth article, we’ll explore:
What a
RIGHT OUTER JOINis.How it compares with the more common
LEFT OUTER JOIN.Whether it provides any unique advantages.
When, if ever, it should be used in practice.
By the end, you’ll have a solid understanding of not only how to use RIGHT OUTER JOIN, but also when it makes sense to do so—and when it doesn’t.
The Basics of JOINs in SQL
Before diving into the specifics of the RIGHT OUTER JOIN, it’s important to quickly review how SQL joins work in general.
SQL joins combine rows from two or more tables based on a related column. The most common joins include:
INNER JOIN: Returns rows where there is a match in both tables.
LEFT OUTER JOIN (
LEFT JOIN): Returns all rows from the left table, and the matching rows from the right table. Non-matching right-hand rows appear asNULL.RIGHT OUTER JOIN (
RIGHT JOIN): Returns all rows from the right table, and the matching rows from the left table. Non-matching left-hand rows appear asNULL.FULL OUTER JOIN: Returns all rows when there is a match in one of the tables. If there is no match, the result is
NULLon the side that lacks data.
We’ll focus exclusively on the RIGHT OUTER JOIN here.
Understanding the RIGHT OUTER JOIN
What It Does
A RIGHT OUTER JOIN returns all rows from the right-hand table (the one listed second in the join clause) and includes matching rows from the left-hand table (the one listed first). If there is no match found in the left table for a row in the right table, the columns from the left table will contain NULL values.
This is essentially the mirror image of a LEFT OUTER JOIN, which does the opposite: it returns all rows from the left table and matching rows from the right.
Basic Syntax
SELECT <columns>
FROM tableA
RIGHT OUTER JOIN tableB
ON tableA.key = tableB.key;
Here, tableA is the left table, and tableB is the right table. The result will include every row from tableB, and where a matching row exists in tableA, data will be filled in accordingly. Where no match exists, values from tableA will be NULL.
A Practical Example
Let’s walk through a concrete, relatable example. Consider two tables:
Employees: a list of employees with the department they belong to.Departments: a list of departments in a company.
Table: Employees
| employee_id | name | department_id |
|---|---|---|
| 1 | Alice | 10 |
| 2 | Bob | 20 |
| 3 | Charlie | NULL |
Table: Departments
| department_id | department_name |
|---|---|
| 10 | Sales |
| 20 | Marketing |
| 30 | HR |
Business Requirement
Suppose we want a list of all departments and the employees who belong to them. This list must include every department—even those that currently have no employees assigned.
We could express this using a RIGHT OUTER JOIN:
SELECT e.name, d.department_name
FROM Employees e
RIGHT OUTER JOIN Departments d
ON e.department_id = d.department_id;
Result
| name | department_name |
|---|---|
| Alice | Sales |
| Bob | Marketing |
| NULL | HR |
This result is telling us:
Alice is in the Sales department.
Bob is in Marketing.
HR has no employees assigned, so the
namecolumn isNULL.
Can We Achieve This with a LEFT OUTER JOIN Instead?
Yes—and that’s where the confusion often comes in.
We can produce the exact same result using a LEFT OUTER JOIN if we simply reverse the order of the tables:
SELECT e.name, d.department_name
FROM Departments d
LEFT OUTER JOIN Employees e
ON e.department_id = d.department_id;
Notice how:
The
Departmentstable is now the left-hand table.The join condition remains the same, but the direction of the outer join is flipped.
Result (Same as Before):
| name | department_name |
|---|---|
| Alice | Sales |
| Bob | Marketing |
| NULL | HR |
So What’s the Point of RIGHT OUTER JOIN?
This is the heart of the matter.
Technically:
There is no difference in the results, performance, or capabilities between a RIGHT OUTER JOIN and a LEFT OUTER JOIN with reversed table order. Every RIGHT JOIN can be rewritten as a LEFT JOIN.
Conceptually:
Here’s where things become more nuanced. While the two are functionally equivalent, using a RIGHT JOIN may make sense in certain contexts:
Clarity of Intent
If you’re mentally treating the right-hand table as the “primary” table (i.e., the one you want to preserve all rows from), using RIGHT OUTER JOIN can make the query more semantically clear to a reader.
Minimizing Query Rewrites
In complex queries—especially those involving nested joins, subqueries, or CTEs—changing the table order to accommodate a LEFT JOIN might require additional alias rewrites or structural changes. Keeping the tables in their original order and using RIGHT JOIN might be simpler.
Consistency in Generated SQL
Some SQL-generating systems (e.g., ORMs or ETL tools) may default to RIGHT JOIN in specific contexts. You may encounter it in legacy code or platform-generated queries.
Legacy or Team Conventions
In some teams or older codebases, RIGHT OUTER JOIN may be used regularly due to personal or organizational preference.
When Should You Use RIGHT OUTER JOIN?
Use it when:
You are preserving the right-hand table by design and wish to make that explicit.
Rewriting the join as a
LEFT JOINwould introduce complexity.You’re working with legacy SQL code that already uses
RIGHT JOIN.Your team or platform favors its usage stylistically.
Avoid it when:
You’re writing new queries from scratch—
LEFT JOINis usually clearer.Readability or maintainability would benefit from a consistent left-to-right join style.
You want to reduce cognitive load for developers who are more accustomed to
LEFT JOIN.
Best Practice Recommendation
In modern SQL development, the vast majority of developers favor LEFT OUTER JOIN for a few reasons:
It aligns with how we read queries: left to right.
The “primary” table is naturally expected on the left.
It reduces ambiguity in multi-join situations.
Most SQL style guides (including those from large engineering organizations) recommend preferring LEFT JOIN over RIGHT JOIN, unless there is a compelling reason to do otherwise.
Conclusion
The RIGHT OUTER JOIN is a perfectly valid SQL construct that mirrors the behavior of the LEFT OUTER JOIN. However, in practice, it is rarely necessary and often avoided in favor of LEFT JOIN, which is generally easier to read, write, and reason about.
That said, understanding RIGHT JOIN is important:
You’ll encounter it in existing codebases.
You may benefit from it in specialized scenarios.
It reinforces your understanding of SQL join symmetry.
But unless there’s a specific reason to use it, most developers will be better served by sticking with LEFT JOIN and putting their “main” table on the left side of the join.
RIGHT OUTER JOINis technically redundant but not useless.Use
LEFT OUTER JOINby default.Use
RIGHT JOINonly when it improves clarity or simplifies complex queries.
Please see the other SQL video learning articles below as part of this learning SQL online course video.
If you would like an instructor lead SQL course that goes into more detail with worked examples then we also provide a video course to learn SQL online hosted on UDEMY.
