Avoid Misusing LEFT JOIN in SQL Queries

INTRO:

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, after all, it helps build flexible screens where filters can be applied dynamically depending on what the user selects in the user interface. But here’s the catch: using LEFT JOIN blindly, without fully understanding what it does under the hood, might lead to confusing results or even slow down your queries. And let’s not forget, it makes life harder for the optimizer trying to figure out the best execution plan. So while LEFT JOIN definitely has its place, it’s worth taking a step back to ask: Do I really need it 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 – Applying Filter Conditions on the Outer Table in the WHERE Clause Instead of the JOIN Clause

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.

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.