6.17.5 WHERE clause

The WHERE clause is made up of conditional expressions used for searching the objects or variables that satisfy the expression. The WHERE clause specifies the result of the select statement and the scope of the update and delete operations.

Organization of this subsection
(1) Conditional expressions that can be used in the WHERE clause
(2) Function expression
(3) Notes

(1) Conditional expressions that can be used in the WHERE clause

The following table describes the conditional expressions that can be used in the WHERE clause.

Table 6-21 Conditional expressions that can be used in the WHERE clause

ExpressionContentsSyntax name of BNF syntax#
BETWEENEvaluates whether the value is included in the scope specified for the field.between_expression
INEvaluates that the value matches with some value specified in the field.in_expression
LIKEEvaluates that the value matches with the string after a wild card sign is allocated in the field.like_expression
IS [NOT] NULLTests whether the value is a null value.null_comparison_expression
IS [NOT] EMPTYTests whether the specified collection value is empty.empty_collection_comparison_expression
[NOT] MEMBER [OF]Tests whether the specified collection value is a collection member. When an empty collection is expressed, the value of the MEMBER OF expression is FALSE and the value of the NOT MEMBER OF expression is TRUE.collection_member_expression
EXISTSDetermines the sub-query result. If one or more values exist, true is returned and in other cases, false is returned.exists_expression
ALLCompares a value with all the values returned by the sub-query.all_or_any_expression
ANY (SOME)Compares a value with some value returned by the sub-query.all_or_any_expression
Sub-queryResults can be coded based on select.subquery
Functional expressionSee (2) Functional expressions.See the syntax of the relevant function

# For details on the syntax name of the BNF syntax, see Appendix D BNF for JPQL.


The notes on using the WHERE clause are as follows:

(2) Function expression

With JPQL, the functions listed in the following table are available in the WHERE clause and HAVING clause. If the value of the function expression argument is null or unknown, the value of the function expression becomes unknown.

Table 6-22 Functions available with JPQL

CategoryFunctionsReturn valueSupplement to arguments
String functionCONCATConcatenated string of the argument--
SUBSTRINGString with the start position and length specified in the argumentThe second and third arguments specify the start position and length of the returned sub-string as an integer. The position of the beginning of the string is 1.
TRIMString with a specific character removedIf no character is to be removed, a space (blank) is assumed. The optional trim character is the character input string. If the trim method is not specified, BOTH is used.
LOWERDe-capitalized string--
UPPERCapitalized string--
LENGTHInteger value of the string length--
LOCATEInteger value of the given string and the position where the string is first found after searching from a specified position (if the string is not found, 0)The first argument indicates the string to be searched and the second argument indicates the searched string. The optional third argument indicates the position of the character to start the search (by default, the search is performed from the beginning). The start position of the string is 1.
Arithmetical functionABSAbsolute value with the same type as the function argument (Integer, Float, or Double)A numerical value is passed as an argument.
SQRTSquare root of the numerical value passed in the argument (real number)A numerical value is passed as an argument.
MODRemainder of two numerical values passed in the argument (integer)Two integers are passed.
SIZENumber of collection elements (integer)Collection is passed.
Date and time functionCURRENT_DATEDatabase date--
CURRENT_TIMEDatabase time--
CURRENT_TIMESTAMPDatabase timestamp--
Legend:
--: Not applicable

(3) Notes

This section describes the notes on the WHERE clause.

(a) Priority of the operators

The priority of the operators is as follows:

  1. Period (.)
  2. Arithmetic operators
    Unary operation (+, -), multiplication and division (*, /), addition and subtraction (+, -)
  3. Comparison operators
    =, >, >=, <, <=, <> (not equal), [NOT] BETWEEN, [NOT] LIKE, [NOT] IN, IS [NOT] NULL, IS [NOT] EMPTY, [NOT] MEMBER [OF]
  4. Logical operators
    NOT, AND, OR
(b) Notes on conditional expressions
(c) Notes on using literals
(d) Notes on identification variables
(e) Notes on path expressions

In the path expression of the collection in the WHERE or HAVING clause, do not use values other than IS [NOT] EMPTY expression and [NOT] MEMBER [OF] expression, or arguments for the SIZE operation as a part of the conditional expression.

(f) Notes on input parameters

For details on how to use the location parameters and the named parameters, see 6.16.1(2) How to specify the parameters.