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 …|
|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.
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)
(x,y) LIKE ALL (SELECT a,b FROM c)
is equivalent to:
(x LIKE c.a) AND (y LIKE c.b)