EXISTS Predicate and NOT IN and Nulls - Teradata Vantage

Teradata® VantageCloud Lake
Deployment
VantageCloud
Edition
Lake
Product
Teradata Vantage
Published
January 2023
ft:locale
en-US
ft:lastEdition
2024-12-11
dita:mapPath
phg1621910019905.ditamap
dita:ditavalPath
pny1626732985837.ditaval
dita:id
phg1621910019905
Use the NOT EXISTS predicate instead of NOT IN if the following conditions are true:
  • A column of the NOT IN condition is defined as nullable.
  • Any rows from the main query with a null in any column of the NOT IN condition must be returned.
  • Any nulls returned in the select list of the subquery must not prevent any rows from the main query from being returned.

For example, if all of the previous conditions are true for the following query, use NOT EXISTS instead of NOT IN:

   SELECT dept, DeptName 
   FROM Department 
   WHERE Dept NOT IN 
    (SELECT Dept 
     FROM Course);

The NOT EXISTS version looks like this:

   SELECT dept, DeptName
   FROM Department 
   WHERE NOT EXISTS
    (SELECT Dept 
     FROM Course 
     WHERE Course.Dept=Department.Dept);

That is, either Course.Dept or Department.Dept is nullable and a row from Department with a null for Dept must be returned and a null in Course.Dept must not prevent rows from Department from being returned.