16.20 - subquery - Advanced SQL Engine - Teradata Database

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.

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

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)