[Solved] This query returns fewer rows than expected:SELECT * FROM A FULL OUTER JOIN B ON A.id = B.id WHERE A.id IS NOT NULL;Why? | PostgreSQL

PostgreSQL - Joins in PostgreSQL

This query returns fewer rows than expected:
SELECT * FROM A FULL OUTER JOIN B ON A.id = B.id WHERE A.id IS NOT NULL;
Why?

AON clause is incorrect

BFULL OUTER JOIN does not work with WHERE clauses

CWHERE clause filters out rows where A.id is NULL, losing unmatched B rows

DA.id cannot be NULL in FULL OUTER JOIN

Step-by-Step Solution

Solution:

Step 1: Understand WHERE filtering effect
WHERE A.id IS NOT NULL removes rows where A.id is NULL, which are unmatched rows from B.
Step 2: Impact on FULL OUTER JOIN result
This filtering reduces rows, losing unmatched rows from B side.
Final Answer:
WHERE clause filters out rows where A.id is NULL, losing unmatched B rows -> Option C
Quick Check:
Filtering NULLs removes unmatched rows = A [OK]

Quick Trick: Filter NULLs carefully after FULL OUTER JOIN to keep all rows [OK]

Common Mistakes:

Master "Joins in PostgreSQL" in PostgreSQL

9 interactive learning modes - each teaches the same concept differently

This query returns fewer rows than expected: