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.
A column mismatch, in which case the candidate is not eliminated by that lookup record.
All columns in the
In the above example, only one candidate record (the last record) is not eliminated, and is therefore returned by the
1 99 NULL
The results of the
NOT IN comparison are as follows:
1, 2, 3was a complete match and triggers rule #2.
1, NULL, 99triggers rule #3 on the second column.
1, 99, NULLtriggers 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
IN predicate on Teradata, the results are different for the two records.