16.20 - subquery - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Functions, Expressions, and Predicates

Product
Advanced SQL Engine
Teradata Database
Release Number
16.20
Release Date
March 2019
Content Type
Programming Reference
Publication ID
B035-1145-162K
Language
English (United States)

If the following form is used, the subquery might return none, one, or several values.



The following example shows how you can match using patterns selected from another table.

There are two base tables.

This table … Defines these things …
Project
  • Unique project ID
  • Project description
Department_Proj The association between project ID patterns and departments.

Department_Proj has two columns: Proj_pattern and Department. The rows in this table look like the following.

Proj_pattern Department
AP% Finance
AR% Finance
Nut% R&D
Screw% R&D

The following query uses LIKE to match patterns selected from the Department_Proj table to select all rows in the Project table that have a Proj_Id that matches project patterns associated with the Finance department as defined in the Department_Proj table.

   SELECT * 
   FROM Project
   WHERE Proj_Id LIKE ANY
    (SELECT Proj_Pattern 
     FROM Department_Proj
     WHERE Department = 'Finance');

When this syntax is used, the subquery must select the same number of expressions as are in the expression list.



For example:

   (x,y) LIKE ALL (SELECT a,b FROM c)

is equivalent to:

   (x LIKE c.a) AND (y LIKE c.b)