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

Teradata Vantage™ - SQL Functions, Expressions, and Predicates

Advanced SQL Engine
Teradata Database
Release Number
Release Date
July 2021
Content Type
Programming Reference
Publication ID
English (United States)

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.

This table … Defines these things …
  • 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
    (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)