NOT IN, NULLs, and the Scalar Subquery That Returns Too Much
Atlas Support has 1,200 employees. HR analytics maintains BONUS_ELIGIBLE(EMPLOYEE_ID NUMBER). A load from three regional systems inserted 3 rows with EMPLOYEE_ID set to NULL. The anti-join query:
SELECT employee_id, last_name FROM employees WHERE employee_id NOT IN (SELECT employee_id FROM bonus_eligible);
returns no rows, although 940 employees are not eligible. A separate report uses a scalar subquery to fetch a department manager's salary, but the subquery can return several rows for a department and fails with ORA-01427. The team also needs the most recent order date per customer from an ORDERS table where some customers have no orders and some have ties on the latest date. Analysts disagree about whether NOT IN or NOT EXISTS should be the standard.
1. Explain precisely why the NOT IN query returns zero rows, then give two rewrites that return the 940 employees.
Read the worked answer
NOT IN (subquery) is equivalent to comparing the outer value to every value in the list with <> and combining with AND. If any value in the list is NULL, one comparison becomes employee_id <> NULL, which evaluates to UNKNOWN. The AND of TRUE and UNKNOWN is UNKNOWN, never TRUE. Since WHERE keeps only TRUE, no row survives when the subquery contains a NULL. This is three-valued logic, not an empty BONUS_ELIGIBLE table.
Null-safe rewrite with NOT EXISTS:
SELECT e.employee_id, e.last_name
FROM employees e
WHERE NOT EXISTS (
SELECT 1 FROM bonus_eligible b
WHERE b.employee_id = e.employee_id
);
Rewrite by removing NULLs from the NOT IN list:
SELECT employee_id, last_name
FROM employees
WHERE employee_id NOT IN (
SELECT employee_id FROM bonus_eligible WHERE employee_id IS NOT NULL
);
Both return 940 rows if 260 employees are eligible. I would also find and fix the three bad rows, then add a NOT NULL constraint or a check so the problem cannot recur. NOT IN is still acceptable when both columns are declared NOT NULL and the data is trusted.
2. What would you standardize on for anti-joins, and how would you validate the source data rather than just rewriting the query?
Read the worked answer
I would standardize on NOT EXISTS for anti-joins because its semantics are null-safe and it directly states 'no related row exists'. It also maps cleanly to an anti-hash join in the optimizer. NOT IN can be efficient, but it carries the NULL trap and is easier for a future developer to break when a nullable column is added.
Validation should include:
SELECT COUNT(*) AS null_ids
FROM bonus_eligible
WHERE employee_id IS NULL;
SELECT employee_id, COUNT(*)
FROM bonus_eligible
GROUP BY employee_id
HAVING COUNT(*) > 1;
Duplicates do not change NOT IN membership, but they usually indicate a missing key constraint. After cleaning the three NULL rows, add ALTER TABLE bonus_eligible MODIFY (employee_id NOT NULL) and a primary key or unique constraint if the business rule allows one. For performance, check the execution plan: both forms should show an anti-join, not a nested loop over 1,200 employees with a repeated subquery. If the plan does not, investigate statistics and indexes on BONUS_ELIGIBLE(EMPLOYEE_ID).
3. Fix the ORA-01427 scalar subquery failure and write a query returning each customer's most recent order, including customers with no orders and handling ties.
Read the worked answer
ORA-01427 means the scalar subquery returned more than one row where the surrounding expression requires exactly one value. A department can have several employees, so (SELECT salary FROM employees WHERE department_id = e.department_id) is not scalar. If only one value is needed, aggregate it, for example (SELECT MAX(salary) FROM employees x WHERE x.department_id = e.department_id). That always returns one row, with NULL when no employee matches.
For the latest order, aggregate first for a date-only answer:
SELECT c.customer_id,
(SELECT MAX(o.order_date)
FROM orders o
WHERE o.customer_id = c.customer_id) AS latest_order_date
FROM customers c;
For the latest order row and tie-breaking by ORDER_ID:
SELECT c.customer_id, o.order_id, o.order_date
FROM customers c
LEFT JOIN (
SELECT order_id, customer_id, order_date,
ROW_NUMBER() OVER (PARTITION BY customer_id
ORDER BY order_date DESC, order_id DESC) AS rn
FROM orders
) o ON o.customer_id = c.customer_id AND o.rn = 1;
The LEFT JOIN preserves customers with no orders. The ROW_NUMBER analytic returns exactly one row per customer. Use RANK instead if all orders tied on the latest date must be returned, accepting multiple rows per customer.