Solution:Step 1: Understand FULL OUTER JOIN with WHERE clauseFULL OUTER JOIN returns all rows, but WHERE filters after join. Filtering on A.name excludes rows where A.name is NULL (unmatched in A).Step 2: Effect of filtering on unmatched rowsRows unmatched in A have NULL for A.name, so WHERE A.name

[Solved] Consider this query:SELECT * FROM A FULL OUTER JOIN B ON A.id = B.id WHERE A.name = 'Alice';Why might this query return fewer rows than expected? | PostgreSQL

PostgreSQL - Joins in PostgreSQL

Consider this query:
SELECT * FROM A FULL OUTER JOIN B ON A.id = B.id WHERE A.name = 'Alice';
Why might this query return fewer rows than expected?

ABecause the WHERE clause filters out rows where A.name is NULL, excluding unmatched rows from B.

BBecause FULL OUTER JOIN only returns matching rows.

CBecause the ON condition is incorrect syntax.

DBecause the query needs GROUP BY to include all rows.

Step-by-Step Solution

Solution:

Step 1: Understand FULL OUTER JOIN with WHERE clause
FULL OUTER JOIN returns all rows, but WHERE filters after join. Filtering on A.name excludes rows where A.name is NULL (unmatched in A).
Step 2: Effect of filtering on unmatched rows
Rows unmatched in A have NULL for A.name, so WHERE A.name = 'Alice' removes them, reducing rows.
Final Answer:
Because the WHERE clause filters out rows where A.name is NULL, excluding unmatched rows from B. -> Option A
Quick Check:
WHERE filters after join, can exclude NULLs [OK]

Quick Trick: Use WHERE carefully; it filters after join, may exclude NULLs [OK]

Common Mistakes:

Thinking FULL OUTER JOIN ignores WHERE filters
Assuming ON clause filters rows after join
Believing GROUP BY affects join row count

Master "Joins in PostgreSQL" in PostgreSQL

9 interactive learning modes - each teaches the same concept differently

Learn Why Deep Visual Try Challenge Project Recall Time

Consider this query:

Step 1: Understand FULL OUTER JOIN with WHERE clause

Step 2: Effect of filtering on unmatched rows

Final Answer:

Quick Check:

Want More Practice?