Code Quiz

LEFT JOIN Silently Becomes INNER JOIN

A CTE-based customer report meant to include zero-order customers accidentally excludes them via a WHERE filter.

Codesql

-- Goal: list EVERY customer with their count of orders in the last 30 days,
-- including customers who placed zero recent orders.
WITH recent_orders AS (
  SELECT customer_id, order_id
  FROM orders
  WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
)
SELECT c.customer_id,
       c.name,
       COUNT(ro.order_id) AS recent_count
FROM customers c
LEFT JOIN recent_orders ro
       ON ro.customer_id = c.customer_id
WHERE ro.order_id IS NOT NULL
GROUP BY c.customer_id, c.name;

The report is supposed to include customers with zero recent orders, but they are missing. What is the bug?

Next →Join Order and Cardinality Estimation