4 / 9 SQL Joins & Subqueries

Rewriting The Scalar Subquery

This runs the subquery once per customer row:

SELECT c.id, c.name,
       (SELECT count(*) FROM orders o WHERE o.customer_id = c.id) AS n_orders
FROM customers c;

Customers with no orders must still appear, with n_orders = 0. Which rewrite produces exactly the same result set?

A 
SELECT c.id, c.name, count(c.id) AS n_orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name;
B 
SELECT c.id, c.name, count(o.id) AS n_orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name;
C 
SELECT c.id, c.name, count(*) AS n_orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name;
D 
SELECT c.id, c.name, count(*) AS n_orders
FROM customers c
JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name;