Left Outer Join vs Right Outer Join In SQL

In this SQL Server tutorial, we will understand the difference between the SQL Server RIGHT OUTER JOIN and LEFT OUTER JOIN clauses on tables, complete with practical examples and best practices.

Left Outer Join vs Right Outer Join In SQL

What is a LEFT OUTER JOIN?

A LEFT OUTER JOIN returns all records from the left table and the matching records from the right table. Think of it as prioritizing the first table you mention in your query. If there’s no match in the right table, NULL values are returned for those columns.

LEFT OUTER JOIN Syntax

SELECT columns
FROM table1
LEFT OUTER JOIN table2
ON table1.column = table2.column;

Example -1

Let’s see an example of SQL Server LEFT OUTER JOIN clause on tables by the following query:

SELECT AUTHORS.*,
BOOKS.BOOK_NAME
FROM AUTHORS
LEFT OUTER JOIN BOOKS
ON AUTHORS.AUTHOR_ID=BOOKS.AUTHOR_ID;

With the help of the LEFT OUTER JOIN query example, we retrieve all records of the author’s details and their book’s name from both tables i.e., AUTHORS and BOOKS.

Example -2: LEFT OUTER JOIN on multiple tables

SELECT AUTHORS.AUTHOR_FULLNAME,
BOOKS.BOOK_NAME,
BOOK_CATEGORY.CATEGORY_NAME
FROM AUTHORS
LEFT OUTER JOIN BOOKS
ON AUTHORS.AUTHOR_ID=BOOKS.AUTHOR_ID
LEFT OUTER JOIN BOOK_CATEGORY
ON BOOKS.BOOK_ID=BOOK_CATEGORY.BOOK_ID;

With the help of the LEFT OUTER JOIN query example, we retrieve the author’s name, the book’s name, and the category’s name from these tables, i.e., AUTHORS, BOOKS, and BOOK_CATEGORY.

What is a RIGHT OUTER JOIN?

A RIGHT OUTER JOIN closely resembles a LEFT OUTER JOIN, except with the treatment of the tables reversed. Every row from the “right” table will appear in the joined table at least once. This means all records from the second table are preserved, regardless of matches in the first table.

RIGHT OUTER JOIN Syntax

SELECT columns
FROM table1
RIGHT OUTER JOIN table2
ON table1.column = table2.column;

Example

Here is an example of SQL Server RIGHT OUTER JOIN on tables by the following query:

SELECT AUTHORS.AUTHOR_FULLNAME,
BOOKS.*
FROM AUTHORS
RIGHT OUTER JOIN BOOKS
ON AUTHORS.AUTHOR_ID=BOOKS.AUTHOR_ID;

With the help of the RIGHT OUTER JOIN in the SELECT statement, we retrieve all records of the author’s name with their book’s details from both tables, i.e., AUTHORS and BOOKS.

Example -2: RIGHT OUTER JOIN on multiple tables

SELECT CUSTOMERS.CUSTOMER_FULLNAME,
BOOKS.BOOK_NAME
FROM ORDERS
RIGHT OUTER JOIN CUSTOMERS
ON ORDERS.ORDER_ID=CUSTOMERS.CUSTOMER_ID
RIGHT OUTER JOIN BOOKS
ON ORDERS.BOOK_ID=BOOKS.BOOK_ID
WHERE CUSTOMERS.CUSTOMER_FULLNAME IS NOT NULL;

In the RIGHT OUTER JOIN query example, we retrieve all records of the customer’s name with their book’s name and order’s ID from these tables, i.e., CUSTOMERS, BOOKS, and ORDERS, by the WHERE condition.

In the WHERE condition, we filtered the customer’s name by a NOT NULL value so that we can retrieve other customers’ names, book names, and order IDs from the result set.

Key Differences Between LEFT and RIGHT OUTER JOIN

Primary Distinctions

Here’s a comprehensive comparison of these two join types:

Aspect	LEFT OUTER JOIN	RIGHT OUTER JOIN
Records Preserved	All from left table	All from right table
NULL Values	In right table columns	In left table columns
Common Usage	Very common (90% of cases)	Less common (10% of cases)
Readability	More intuitive	Can be confusing
Performance	Generally consistent	Generally consistent

When to Use Each Join Type

Use LEFT OUTER JOIN when:

• You want all records from your primary table
• You’re looking for “master” data with optional related information
• You need to find records without matches (orphaned records)
• You want to maintain logical query flow

Use RIGHT OUTER JOIN when:

• You want all records from the second table
• You’re working with legacy queries that follow this pattern
• You need to reverse the perspective of a LEFT JOIN

Best Practices Summary

Based on my extensive experience with SQL databases in enterprise environments:

Do:

• Use meaningful table aliases
• Prefer LEFT OUTER JOIN over RIGHT OUTER JOIN
• Index your join columns appropriately
• Handle NULL values explicitly
• Test with realistic data volumes

Don’t:

• Mix inner and outer joins without understanding the impact
• Ignore NULL value handling
• Use outer joins when inner joins suffice
• Forget to optimize for performance

Read: SQL Server LEFT OUTER JOIN with WHERE clause

Read: SQL Server Left Join With Count

Conclusion

Understanding the differences between LEFT OUTER JOIN and RIGHT OUTER JOIN is crucial for any database developer. While both serve important purposes, LEFT OUTER JOIN is generally preferred for its readability and intuitive behavior.

Knowing these concepts clearly leads to more efficient queries, better performance, and more maintainable code.

You may also like to read the following SQL Server tutorials.

• SQL Server SELF JOIN 
• SQL Server OUTER JOIN
• SQL Server Inner Join Top 1