Question 200
The NoticeEvents table may contain NULL recipient identifiers. A team proposes a correlated NOT EXISTS query to find users without a matching required-notice delivery. Why is this safer than an unguarded NOT IN over recipient identifiers?
Explanation
Correct Answer
For each user, the correlated subquery checks whether a delivery row matching that user and notice exists. NOT EXISTS is based on the existence of such rows, whereas NOT IN can yield UNKNOWN when its subquery includes NULL.
Incorrect Answers
A NULL in the NOT IN result set can make comparisons UNKNOWN, preventing expected unmatched rows from being returned. The operator does not automatically discard NULLs.
Without a recipient correlation, NOT EXISTS tests the whole table for the notice rather than absence for the current user; it can return all users or none.
An unrelated notice is not proof that this user received the mandated notice. The predicate must include the required notice code and delivery condition.