subquery

Teradata Vantage™ SQL Functions, Expressions, and Predicates

brand
Software
Teradata Vantage
prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1145-162K

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)