DWHPro Insights

Teradata Antiselect: Selecting All Columns Except the Ones You Exclude

By Roland Wenzlofsky ·

Teradata Antiselect: Selecting All Columns Except the Ones You Exclude

The comparison section supplies both three-column target definitions; provide the OldMapping and NewMapping source tables. Run the separate demonstrations with unused table names. Two directional MINUS checks compare distinct rows; they do not prove that duplicate multiplicities match.

Teradata has incorporated several analytical functions from Teradata Aster in its recent releases. Although many of these functions are tailored for web click analysis, Teradata Antiselect has proven valuable for specific applications.

What is the Teradata Antiselect function?

Teradata’s Antiselect function allows for the reversal of column selection logic. Rather than specifying columns to select, Antiselect can be used to specify columns that should not be included in the result set, leaving all other columns present.

What is the syntax of Teradata Antiselect?

Below is our table example:

CREATE  TABLE  DWHPRO.AntiSelect (  a BIGINT,  b BIGINT,  c BIGINT,  d BIGINT,  e BIGINT,  f BIGINT,  g BIGINT,  h BIGINT ) PRIMARY  INDEX  (a);

All columns are selected from the table “DWHPRO.Antiselect” except for columns c and e.

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book
SELECT  * FROM  Antiselect (  ON  DWHPRO.AntiSelect  USING  Exclude ('c','e') ) AS  anti ;

A standard SQL query would appear as follows:

SELECT  a,b,d,f,g,h FROM  DWHPRO.AntiSelect;

What are useful applications for Teradata Antiselect?

Sometimes specific technical columns must be omitted from a comparison to verify whether two tables match. For instance, in our scenario column c is populated with the CURRENT_TIMESTAMP function, which will inevitably produce a different value every time the table is loaded.

CREATE TABLE DWHPRO.AntiSelect1
(a BIGINT, b BIGINT, c TIMESTAMP) PRIMARY INDEX(a);

CREATE TABLE DWHPRO.AntiSelect2
(a BIGINT, b BIGINT, c TIMESTAMP) PRIMARY INDEX(a);
INSERT INTO DWHPRO.AntiSelect1 SELECT colA,colB,CURRENT_TIMESTAMP  FROM OldMapping;
INSERT INTO DWHPRO.AntiSelect2 SELECT colA,colB,CURRENT_TIMESTAMP  FROM NewMapping;
SELECT  * FROM  Antiselect (ON  DWHPRO.AntiSelect1 USING  Exclude ('c') ) AS  anti MINUS  SELECT  * FROM  Antiselect (ON  DWHPRO.AntiSelect2 USING  Exclude ('c') ) AS  anti;
SELECT  * FROM  Antiselect (ON  DWHPRO.AntiSelect2 USING  Exclude ('c') ) AS  anti MINUS  SELECT  * FROM  Antiselect (ON  DWHPRO.AntiSelect1 USING  Exclude ('c') ) AS  anti;

Teradata Antiselect is particularly convenient when dealing with tables with many columns.

What are the possibilities for excluding columns?

This is our sample table:

CREATE  TABLE  DWHPRO.AntiSelect(a BIGINT,b BIGINT,c BIGINT,d BIGINT,e BIGINT,f BIGINT,g BIGINT,h BIGINT) PRIMARY  INDEX  (a);

A list of columns can be excluded with the following syntax (columns c and e are excluded):

SELECT  * FROM  Antiselect (ON  DWHPRO.AntiSelect USING  Exclude ('c','e') ) AS  anti ;

A range of columns can be excluded with the following syntax: (columns c,d,e are excluded):

SELECT  * FROM  Antiselect ( ON  DWHPRO.AntiSelect USING  Exclude ('c:e') ) AS  anti ;

A range of columns can be excluded by index with the following syntax: (columns b,c,d are excluded); indexing starts with the position 0:

SELECT  * FROM  Antiselect (ON  DWHPRO.AntiSelect USING  Exclude ('[1:3]') ) AS  anti ;

Exclude columns from zero-based index 2 through the end: c,d,e,f,g,h are excluded; a,b remain.

SELECT  * FROM  Antiselect (ON  DWHPRO.AntiSelect USING  Exclude ('[2:]') ) AS  anti ;

Exclude columns from the beginning through zero-based index 2: a,b,c are excluded; d,e,f,g,h remain.

SELECT  * FROM  Antiselect (ON  DWHPRO.AntiSelect USING  Exclude ('[:2]') ) AS  anti ;

What is the performance impact of using Teradata Antiselect?

Selecting explicit columns and using Antiselect can yield similar data access, but operator overhead and plans depend on the workload and release. The earlier 2–4× CPU observation was specific to a historical test and should not be used as a general multiplier.

Deciding what to change in your data platform?

Compare optimisation, modernisation and migration against your workload and business requirements.

Explore independent platform assessments

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Follow DWHPro in Google to see our articles more often in Search.

Leave a Comment

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Protected by Security by CleanTalk and CleanTalk Anti-Spam