TL;DR:
If you’re using a LEFT JOIN but filtering on the right table in the WHERE clause, you might unintentionally turn it into an INNER JOIN. Here’s why that happens, how to fix it, and how different databases handle it.

---

🧩 The Issue: LEFT JOIN + WHERE = INNER JOIN?

Let’s start with the basics. The purpose of a LEFT JOIN is to return:

• All records from the left table
• Matching records from the right table
• NULLs from the right table if there’s no match

So, you expect unmatched rows to still appear.

But what if you write this query?

sqlCopyEditSELECT *
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id
WHERE d.name = 'Sales';

Surprise!
This behaves just like an INNER JOIN. All employees who aren’t in the “Sales” department — or who have no department at all — are filtered out. That’s because:

WHERE d.name = 'Sales' excludes any row where d.name is NULL, which happens for unmatched joins.

---

🛠️ Fixes: How to Keep the LEFT JOIN Behavior

✅ Fix #1: Move Conditions into the JOIN Clause

Move the right-table filter into the ON clause:

sqlCopyEditSELECT *
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id AND d.name = 'Sales';

🔍 This ensures:

• Unmatched employees (those with no department) still appear.
• Only “Sales” departments are matched, others are treated as non-matches (NULLs).

---

✅ Fix #2: Use IS NULL in the WHERE Clause (Conditional Logic)

If your goal is to check for unmatched rows specifically, use IS NULL:

sqlCopyEditSELECT *
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id
WHERE d.id IS NULL;

🟡 This returns only employees with no matching department — often used for “find orphans” type queries.

---

✅ Fix #3: Use CASE WHEN for Conditional Filtering

If you want a more nuanced condition (e.g. show all employees, but flag those in Sales), try this:

sqlCopyEditSELECT e.name,
       CASE WHEN d.name = 'Sales' THEN 'Sales' ELSE 'Other/None' END AS dept_category
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;

✅ Keeps all employees, gives you a flag you can use later.

Behavior Across SQL Dialects

Database	Behavior of LEFT JOIN + WHERE on right table	Notes
PostgreSQL	Strict — behaves like INNER JOIN	You must filter right-table columns inside JOIN to preserve NULLs
MySQL	Same as Postgres	Right-table filters in WHERE clause remove unmatched rows
SQL Server	Same — WHERE acts after JOIN	Use OUTER APPLY for more advanced conditional joins
Oracle	Same behavior; legacy syntax uses (+)=	ANSI JOINs recommended for modern, predictable behavior
Hive SQL	Behaves like INNER JOIN when filtering in WHERE	Join optimization may push predicates down — always put filters in ON clause for LEFT JOIN correctness

Summary

Query Pattern	Result Type
LEFT JOIN + WHERE on left table only	Preserves LEFT JOIN
LEFT JOIN + WHERE on right table	Behaves like INNER JOIN
LEFT JOIN + ON with right-table filter	Preserves unmatched rows
LEFT JOIN + CASE WHEN	Useful for conditional logic

---

🧭 Final Thoughts

SQL behavior is all about order of operations:

1. Joins happen first.
2. WHERE filters apply next.
3. So if your WHERE clause filters out NULLs, you lose unmatched rows.

Always ask yourself: Do I want unmatched rows to appear? If yes, make sure right-side filters go into the JOIN clause — not WHERE.

---

📌 Pro Tip

You can test if a LEFT JOIN is still “lefty” by temporarily adding:

sqlCopyEditWHERE d.id IS NULL

If the result is empty, your join likely became an INNER JOIN due to misplaced filtering.