假如需要显示customers表中每个客户的订单总数。

子查询

(1) 从customers表中检索客户列表。

(2) 对于检索出的每个客户，统计其在orders表中的订单数目。

对于检索出的10001客户，统计其在orders表中的订单数目。

SELECT 	COUNT(*)
FROM orders
WHERE orders.cust_id = 10001

为了对每个客户执行COUNT()计算，应该将COUNT()作为一个子查询。

SELECT
	customers.cust_name,
	customers.cust_contact,
	( SELECT COUNT( * ) FROM orders WHERE orders.cust_id = customers.cust_id ) AS total_orders 
FROM
	customers;
	ORDER BY customers.cust_name;

联结

SELECT
	customers.cust_name,
	customers.cust_contact,
	total_numbers 
FROM
	customers
	LEFT JOIN ( SELECT orders.cust_id, COUNT( * ) AS total_numbers FROM orders GROUP BY orders.cust_id ) AS total_numbers_cust 
	ON total_numbers_cust.cust_id = customers.cust_id;

❌存在NULL问题