NOT IN
To the right of the operator is the subquery in parentheses, which must return the contents of only one column. We compute the expression on the left side of the operator and compare it with each row in the subquery result. The result of the NOT IN operation is "true" if it returns only rows that are not equal to those in the subquery (including the specific case where the subquery returns no rows). If the same row is found, then the result is "false".
expression NOT IN (subquery)
Note that if the expression on the left side of the operator produces a null value, or if there is no row in the table on the right side of the operator that is not equal to it, and the subquery on the right side of the operator returns at least one row that produces a null value, then the result of the NOT IN construct will be null, not true. This is consistent with the general SQL rule for combining null values with Boolean variables.
As with EXISTS, it is not wise to assume that the subquery will complete the query for all rows involved.
Top comments (0)