Avoid Misusing LEFT JOIN in SQL Queries

Unfortunately, it’s surprisingly common to see LEFT JOIN used in almost every SQL query, whether it’s really necessary or not. Developers often reach for it because it seems like the safe choice, especially when building flexible application screens where filters are applied dynamically based on user selections.

But here’s the catch: using LEFT JOIN blindly, without fully understanding how it works, can lead to unexpected results. One particularly common mistake is placing a filter on the right-hand table in the WHERE clause, unintentionally eliminating the unmatched rows that the LEFT JOIN was supposed to preserve. In some cases, the query effectively becomes an INNER JOIN, making the original choice of join type unnecessary.

In this article, I’ll demonstrate two common LEFT JOIN pitfalls using Oracle Database 23ai and the SH sample schema. Through practical SQL examples, we’ll see how filter placement changes query results, how Oracle’s optimizer handles these joins, and why it’s worth asking one simple question before writing a query: Do I really need a LEFT JOIN here?

Here is the meme :

In this post, I’ll cover two common misuses of LEFT JOIN in Oracle SQL that I’ve seen repeatedly.

Pitfall 1 – Using LEFT JOIN When You Actually Need INNER JOIN

I will be using Oracle Database 23ai Free – Version 23.6.0.24.10 with the SH sample schema. You can refer to my blog post, “Installing Oracle Database 23ai on ARM Macs with Sample Schemas,” for guidance on preparing the environment.

Here is the example:

Requirement 1: We need the sales record of INACTIVE customers:

SQL> SELECT s.cust_id, c.cust_first_name, c.cust_last_name, s.amount_sold
FROM sh.sales s
LEFT JOIN sh.customers c ON s.cust_id = c.cust_id
WHERE c.cust_valid = 'I';

What’s the issue?
We used a LEFT JOIN, which means “get all sales, even if there’s no matching customer.” But the WHERE clause filters only inactive customers with c.cust_valid = ‘I’.
If there’s no matching customer, then c.cust_valid will be NULL, which means all the rows that were supposed to come from the sales table without a match from customers will be eliminated.

So, what we’ve written is no different from:

SQL> SELECT s.cust_id, c.cust_first_name, c.cust_last_name, s.amount_sold
FROM sh.sales s
INNER JOIN sh.customers c ON s.cust_id = c.cust_id
WHERE c.cust_valid = 'I';

We might as well use an INNER JOIN directly, it makes the intent of the query clearer. While Oracle optimizer is smart enough to perform such transformations automatically, using an INNER JOIN explicitly can actually make things easier for the optimizer. It opens the door to more potential execution plans, which can ultimately lead to better performance.

Whenever I get confused with outer joins or have a hard time converting Oracle’s native outer join syntax (+) to ANSI syntax, I refer to Maria Colgan’s blog post “Outerjoins in Oracle.”

Pitfall 2: Filtering the Right-Hand Table in WHERE Instead of ON

Requirement 2: Return all sales and include customer names only if the customer is inactive.

Here is an example of an INCORRECT query that might be written for Requirement 2.

SQL> SELECT s.cust_id, c.cust_first_name, c.cust_last_name, s.amount_sold
FROM sh.sales s
LEFT JOIN sh.customers c ON s.cust_id = c.cust_id
WHERE c.cust_valid = 'I';

Although Requirements 1 and 2 are different, most of the time, the same SQL was incorrectly reused for both.

What it should be ?

SQL> SELECT s.cust_id, c.cust_first_name, c.cust_last_name, s.amount_sold
FROM sh.sales s
LEFT JOIN sh.customers c 
  ON (s.cust_id = c.cust_id AND c.cust_valid = 'I');

Oracle’s native outer join equivalent:

SQL> SELECT s.cust_id, c.cust_first_name, c.cust_last_name, s.amount_sold
FROM sh.sales s, sh.customers c
WHERE s.cust_id = c.cust_id(+) AND c.cust_valid(+) = 'I';

Since we only need data from the customers table when the customer is INACTIVE, the condition should be placed in the JOIN rather than the WHERE clause.

The important distinction is not simply whether a condition belongs in the WHERE or ON clause, but what the query is expected to return. If we need only sales belonging to inactive customers, an INNER JOIN expresses that requirement clearly. If we need every sale but want customer details only for inactive customers, the filter belongs in the LEFT JOIN condition.

Footnote : What is the Recommended Outer Join Style – ANSI outer join or Oracle Outer Join with (+)

This question is already answered by Chris Saxon on ASKTOM. His answer is :

Oracle style joins may have been recommended in the past, but the current guidance is:

Oracle recommends that you use the FROM clause OUTER JOIN syntax rather than the Oracle join operator. Outer join queries that use the Oracle join operator (+) are subject to the following rules and restrictions, which do not apply to the FROM clause OUTER JOIN syntax:

https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/Joins.html

Hope it helps.

Also my other Common SQL/PLSQL Pitfalls – Blog Posts:

Common SQL and PL/SQL Pitfalls: Insights from Real-World Experience

NVL and DECODE: Lazy vs Eager Evaluation (Part 1)

Scalar Subquery Caching Behavior in a SQL Statement (Part 2)

Why NO_DATA_FOUND Behavior Differs in SQL and PL/SQL (Part 3)

Oracle SUM() Returns NULL or No Rows? GROUP BY Explained (Part 4)

Avoid Misusing LEFT JOIN in SQL Queries (Part 5)