17.10 - Example: Matching Patterns from Another Table - Advanced SQL Engine - Teradata Database

The following form of subquery might return none, one, or several values:

expr [ NOT ] LIKE quantifier (subquery)

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:

( expr [,...] ) [ NOT ] LIKE quantifier (subquery)

For example:

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

is equivalent to:

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