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