Every "show all customers including those without orders" report uses LEFT JOIN. Marketing needs all customers (even those who never bought) to send promotions. Finance needs all accounts (even with zero transactions) for compliance. HR needs all employees (even without performance reviews) for annual reports. LEFT JOIN is the second most common join type after INNER JOIN - used whenever you need complete data including rows without matches. FULL OUTER JOIN finds orphaned records on both sides: orders without customers AND customers without orders.

Customer Engagement Analysis - All Customers Including Inactive

Marketing dashboard showing all customers with purchase history. Must include customers who never bought anything for targeted campaigns.

E-commerce

SELECT

c.id,

c.name,

c.email,

c.signup_date,

COUNT(o.id) AS order_count,

COALESCE(SUM(o.total), 0) AS lifetime_value,

COALESCE(MAX(o.order_date), c.signup_date) AS last_activity,

DATEDIFF(CURRENT_DATE, COALESCE(MAX(o.order_date), c.signup_date)) AS days_since_activity,

CASE

WHEN COUNT(o.id) = 0 THEN 'Never Purchased'

WHEN MAX(o.order_date) < CURRENT_DATE - INTERVAL '90 days' THEN 'Inactive'

WHEN COUNT(o.id) >= 10 THEN 'VIP'

ELSE 'Active'

END AS customer_segment

FROM customers c

LEFT JOIN orders o ON c.id = o.customer_id

GROUP BY c.id, c.name, c.email, c.signup_date

ORDER BY lifetime_value DESC;

LEFT JOIN ensures ALL customers appear in report. Marketing can segment: (1) Never purchased - send welcome discount, (2) Inactive - re-engagement campaign, (3) VIP - loyalty rewards. COUNT(o.id) gives accurate zero counts. COALESCE handles NULL for customers without orders. This query powers customer segmentation used daily by marketing team.

All

Feature Usage Report - All Users Including Non-Active

Product analytics showing feature usage for all users. Must include users who never used the feature to identify adoption gaps.

SaaS Platform

SELECT

u.id,

u.email,

u.plan_tier,

u.signup_date,

COUNT(f.id) AS feature_usage_count,

COALESCE(MAX(f.used_at), u.signup_date) AS last_feature_use,

CASE

WHEN COUNT(f.id) = 0 THEN 'Never Used'

WHEN MAX(f.used_at) < CURRENT_DATE - INTERVAL '30 days' THEN 'Dormant'

WHEN COUNT(f.id) >= 50 THEN 'Power User'

ELSE 'Regular User'