Subqueries Used in Search Conditions

Subqueries are used as search conditions in order to filter results. They specify the conditions for the rows returned from the containing query's select-list, a query expression, or the subquery itself. The operation evaluates to TRUE, FALSE, or UNKNOWN (NULL).

Syntax

search‑condition {
    [ { AND | OR [ NOT ] } {  predicate  | ( search‑condition ) } ] 
   } [ ,... ]
 predicate
     { expression comparison‑operator expression
     ... | string‑expression [ NOT ] { LIKE | ILIKE | LIKEB | ILIKEB } string-expression
     ... | expression IS [ NOT ] NULL
     ... | expression [ NOT ] IN ( subquery | expression [ ,...n ] )
     ... | expression comparison‑operator [ ANY | SOME ] ( subquery )
     ... | expression comparison‑operator ALL ( subquery )
     ... | expression OR ( subquery )
     ... | [ NOT ] EXISTS ( subquery )
     ... | [ NOT ] IN ( subquery )
     } 

Parameters

search-condition 

Specifies the search conditions for the rows returned from one of the following:

  • Containing query's select-list
  • Query expression
  • Subquery

If the subquery is used with an UPDATE or DELETE statement, UPDATE specifies the rows to update and DELETE specifies the rows to delete.

{ AND | OR | NOT }

Keywords that specify the logical operators that combine conditions, or in the case of NOT, negate conditions.

  • AND — Combines two conditions and evaluates to TRUE when both of the conditions are TRUE.
  • OR — Combines two conditions and evaluates to TRUE when either condition is TRUE.
  • NOT — Negates the Boolean expression specified by the predicate.
predicate

An expression that returns TRUE, FALSE, or UNKNOWN (NULL).

expression

A column name, constant, functiona scalar subquery, or combination of column names, constants, and functions connected by operators or subqueries.

comparison‑operator

Test conditions between expressions, one of the following operators:

  • < tests the condition of one expression being less than the other.
  • > tests the condition of one expression being greater than the other.
  • <= tests the condition of one expression being less than or equal to the other expression.
  • >= tests the condition of one expression being greater than or equal to the other expression.
  • = tests the equality between two expressions.
  • <=> tests equality like the = operator, but it returns TRUE instead of UNKNOWN if both operands are UNKNOWN and FALSE instead of UNKNOWN if one operand is UNKNOWN.
  • <> and != test the condition of two expressions not equal to one another.
string‑expression  

A character string with optional wildcard (*) characters.

[ NOT ] { LIKE | ILIKE | LIKEB | ILIKEB }

Indicates that the character string following the predicate is to be used (or not used) for pattern matching.

IS [ NOT ] NULL

Searches for values that are null or are not null.

ALL

Used with a comparison operator and a subquery. Returns TRUE for the lefthand predicate if all values returned by the subquery satisfy the comparison operation, or FALSE if not all values satisfy the comparison or if the subquery returns no rows to the outer query block.

ANY | SOME

ANY and SOME are synonyms and are used with a comparison operator and a subquery. Either returns TRUE for the lefthand predicate if any value returned by the subquery satisfies the comparison operation, or FALSE if no values in the subquery satisfy the comparison or if the subquery returns no rows to the outer query block. Otherwise, the expression is UNKNOWN.

[ NOT ] EXISTS

Used with a subquery to test for the existence of records that the subquery returns.

[ NOT ] IN

Searches for an expression on the basis of an expression's exclusion or inclusion from a list. The list of values is enclosed in parentheses and can be a subquery or a set of constants.