[Solved] A query on a star schema returns fewer rows than expected:SELECT d.Region, SUM(f.Sales) FROM FactSales f LEFT JOIN DimStore d ON f.StoreID = d.StoreID WHERE d.Region = 'West' GROUP BY d.... | SQL

SQL - Database Design and Normalization

A query on a star schema returns fewer rows than expected:

SELECT d.Region, SUM(f.Sales) FROM FactSales f LEFT JOIN DimStore d ON f.StoreID = d.StoreID WHERE d.Region = 'West' GROUP BY d.Region;

What is the likely cause?

AFact table has no matching StoreID values

BSUM aggregation is incorrect

CFiltering on dimension column in WHERE clause converts LEFT JOIN to INNER JOIN

DGROUP BY clause is missing

Step-by-Step Solution

Solution:

Step 1: Analyze LEFT JOIN with WHERE filter
Filtering on d.Region in WHERE removes rows where d.Region is NULL, effectively making LEFT JOIN behave like INNER JOIN.
Step 2: Understand impact on row count
This reduces rows returned, causing fewer results than expected.
Final Answer:
Filtering on dimension column in WHERE clause converts LEFT JOIN to INNER JOIN -> Option C
Quick Check:
WHERE on dimension after LEFT JOIN filters out NULLs [OK]

Quick Trick: Filter LEFT JOIN columns in ON, not WHERE, to keep all rows [OK]

Common Mistakes:

Master "Database Design and Normalization" in SQL

9 interactive learning modes - each teaches the same concept differently

More SQL Quizzes

A query on a star schema returns fewer rows than expected: