Question

You are building a reporting query in a Fabric warehouse that combines a DimCustomer table (all registered customers) with a FactOrder table (only customers who placed at least one order). Business requires the output to list every registered customer, showing their total order count, with zero displayed for customers who have never ordered. Which join and function combination should you use in your T-SQL query?

Accepted Answer

LEFT JOIN FactOrder to DimCustomer, using ISNULL/COALESCE to convert the aggregated NULL count to 0

Answer

INNER JOIN DimCustomer to FactOrder so only matched customers are counted accurately

Answer

RIGHT JOIN DimCustomer to FactOrder, relying on the aggregate to include all customers automatically

Answer

CROSS JOIN DimCustomer and FactOrder, then filter with a WHERE clause on customer key