SQL查询问题：求Ann Hull代理且无当前订单的客户编号与名称

Let’s break down how to get the correct list of customers represented by Ann Hull who have no active/valid orders. I’ll cover two reliable approaches and point out common pitfalls that might be messing up your current query.

Common Mistakes to Avoid #

First, check if you’re making any of these errors:

• Using an INNER JOIN with the orders table: This will automatically exclude customers who don’t have any orders, which isn’t what you want.
• Putting the "valid order" condition in the WHERE clause instead of the JOIN clause: If you’re using a LEFT JOIN, this converts it to an INNER JOIN and filters out the NULLs you need to identify customers with no orders.

Approach 1: LEFT JOIN + IS NULL #

This method keeps all customers linked to Ann Hull, then filters out those who have at least one valid order:

SELECT c.customer_id, c.name
FROM customers c
-- Link customers to their agent
JOIN agents a ON c.agent_id = a.agent_id
-- Left join to include all customers, even those with no orders
LEFT JOIN orders o 
  ON c.customer_id = o.customer_id 
  AND o.is_valid = 1 -- Replace with your actual "valid order" condition (e.g., status = 'active')
WHERE a.agent_name = 'Ann Hull'
-- Only keep customers where no valid order exists
AND o.order_id IS NULL;

Approach 2: NOT EXISTS (Often More Efficient) #

This uses a subquery to check that there are no valid orders for the customer. It’s usually faster for large datasets because it stops searching as soon as it finds a matching order:

SELECT c.customer_id, c.name
FROM customers c
JOIN agents a ON c.agent_id = a.agent_id
WHERE a.agent_name = 'Ann Hull'
AND NOT EXISTS (
    SELECT 1 
    FROM orders o
    WHERE o.customer_id = c.customer_id
    AND o.is_valid = 1 -- Adjust this to match your definition of a "valid" order
);

Key Notes #

• Replace o.is_valid = 1 with whatever condition defines a "valid" order in your schema—this could be order_status = 'Active', expiry_date > CURRENT_DATE, etc.
• Make sure your agents table has an entry for "Ann Hull" (check for typos, like extra spaces or case sensitivity if your database is case-sensitive).

内容的提问来源于stack exchange，提问作者chris