Northwind数据库：查询指定日期前3天无美国客户销售的员工

Let's walk through the issues with your current SQL and get it working as expected.

Key Problems in Your Original Query #

• Incorrect date column: You referenced RequiredDate, but your sample data uses Orderdate—this is a critical typo that would exclude all valid order records.
• Misused FULL JOIN: A FULL JOIN between Orders and Customers introduces NULL values for EmployeeID (for customers with no orders). Since NOT IN can't handle NULLs reliably (any comparison with NULL returns UNKNOWN, making the entire condition fail), this leads to an empty result set. We only care about orders linked to US customers, so INNER JOIN is the right choice here.
• Join column mismatch: Your Orders table uses CustomerName (e.g., C1, C2) but your join uses o.CustomerID = c.CustomerID—this prevents any valid matches between the tables.
• Incomplete date filter: Your condition only checks for dates after the 3-day mark, but doesn't cap it at 1997-01-25. This could include orders outside your target window if they exist.

Corrected SQL Query #

First, here's a fixed version using NOT IN (with all issues resolved):

SELECT EmployeeID
FROM Employees
WHERE EmployeeID NOT IN (
    SELECT o.EmployeeId
    FROM Orders o
    INNER JOIN Customers c ON o.CustomerName = c.CustomerID -- Fix join column mismatch
    WHERE c.Country = 'USA'
      AND o.Orderdate >= DATEADD(day, -3, '1997-01-25')
      AND o.Orderdate <= '1997-01-25' -- Restrict to the target date window
)

Alternatively, using NOT EXISTS (more robust against NULLs, even if not strictly needed here):

SELECT e.EmployeeID
FROM Employees e
WHERE NOT EXISTS (
    SELECT 1
    FROM Orders o
    INNER JOIN Customers c ON o.CustomerName = c.CustomerID
    WHERE o.EmployeeId = e.EmployeeID
      AND c.Country = 'USA'
      AND o.Orderdate BETWEEN DATEADD(day, -3, '1997-01-25') AND '1997-01-25'
)

How This Works #

1. INNER JOIN: Ensures we only retrieve orders that are directly linked to US customers, eliminating any NULL values in the subquery results.
2. Fixed join columns: Matches CustomerName from Orders to CustomerID from Customers (adjust this to o.CustomerID = c.CustomerID if your actual schema uses consistent column names).
3. Date range: The BETWEEN (or >= + <=) clause restricts orders to the 3-day window ending on 1997-01-25.
4. NOT IN/NOT EXISTS: Filters out employees who have at least one US sale in that window, leaving exactly the employees you expect (2, 3, 5).

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