Left Join to Include Users Without Orders

SQL

Technical Specification

List all users and their total order_count, including users who have never placed an order.

Input/Output Samples

Input:users + orders

Output:{ user, order_count } (including zeros)

Optimal Logic Path

SELECT 
  u.id,
  u.name,
  COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o
  ON o.user_id = u.id
GROUP BY u.id, u.name;

Architectural Deep-Dive

LEFT JOIN keeps all users even if there is no matching order row; COUNT(o.id) counts actual orders.