Semantics for Teradata Vector NOT IN Predicates – Datometry

Teradata supports multi-column NOT IN predicates, the semantics of which may not be
evident when nullable columns are involved.

This article illustrates multi-column (or vector) NOT IN semantics for Teradata using the following example:

CREATE MULTISET TABLE mduller.vectornotinsrc (i INTEGER, j INTEGER, k INTEGER);
INSERT INTO mduller.vectornotinsrc (1, 2, 3);
INSERT INTO mduller.vectornotinsrc (1, NULL, 99);
INSERT INTO mduller.vectornotinsrc (1, 99, NULL);

CREATE MULTISET TABLE mduller.vectornotinlookup (i INTEGER, j INTEGER, k INTEGER);
INSERT INTO mduller.vectornotinlookup (1, 2, 3);

SELECT * FROM mduller.vectornotinsrc
WHERE (i, j, k) NOT IN (SELECT i, j, k FROM mduller.vectornotinlookup);

For every candidate record in the table vectornotinsrc, the columns are compared from left to right with all of the lookup records in the NOT IN clause. If a column matches, the comparison moves to the next column until one of the rules in the table below is met.

Rule	Description
Rule 1	A column mismatch, in which case the candidate is not eliminated by that lookup record.
Rule 2	All columns in the `NOT IN` clause have been processed and all match, in which case the candidate record is eliminated by that lookup record.
Rule 3	A `NULL` value is found in either the candidate or the lookup record, in which case the candidate record is eliminated by that lookup record.

In the above example, only one candidate record (the last record) is not eliminated, and is therefore returned by the SELECT statement:

1     99    NULL

The results of the NOT IN comparison are as follows:

Record 1, 2, 3 was a complete match and triggers rule #2.
Record 1, NULL, 99 triggers rule #3 on the second column.
Record 1, 99, NULL triggers rule #1 on the second column.

Note: While the records 1, NULL, 99 and 1, 99, NULL are symmetric in that they have one non-matching column value (99) and one NULL column, due to the left-to-right semantics of the NOT IN predicate on Teradata, the results are different for the two records.

Related articles