9.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
The following table describes the conditional expressions that can be used in the WHERE clause.
|
Expression |
Contents |
Syntax name of BNF syntax# |
|---|---|---|
|
BETWEEN |
Evaluates whether the value is included in the scope specified for the field. |
between_expression |
|
IN |
Evaluates that the value matches with some value specified in the field. |
in_expression |
|
LIKE |
Evaluates that the value matches with the string after a wild card sign is allocated in the field. |
like_expression |
|
IS [NOT] NULL |
Tests whether the value is a null value. |
null_comparison_expression |
|
IS [NOT] EMPTY |
Tests 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 |
|
EXISTS |
Determines the sub-query result. If one or more values exist, true is returned and in other cases, false is returned. |
exists_expression |
|
ALL |
Compares 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-query |
Results can be coded based on select. |
subquery |
|
Functional expression |
See (2) Functional expressions. |
See the syntax of the relevant function |
- #
-
For details on the syntax name of the BNF syntax, see Appendix G BNF for JPQL.
The notes on using the WHERE clause are as follows:
-
IN expression
-
If the field to be evaluated is null or unknown, the expression value becomes unknown.
-
The list that defines the value set, demarcated by commas, for the IN expression, must have at least one or more elements. In Cosminexus JPA Provider, an exception occurs if one or more elements do not exist.
-
The field to be evaluated must have a string, numeric, or enum value.
-
The literal and input parameter values, and the sub-query results must have the same abstract schema type as the field to be evaluated. In Cosminexus JPA Provider, an exception occurs if the abstract schema type is not the same.
-
-
LIKE expression
-
If the value of string_expression or pattern_value indicated in the BNF syntax is null or unknown, the value of the LIKE expression becomes unknown.
-
If escape_character indicated in the BNF syntax is specified and if the value is null, the value of the LIKE expression becomes unknown.
-
In the specified pattern value, an underscore (_) corresponds to one character and a percent (%) character corresponds to continuous characters (including continuous null characters). Note that the other characters indicate the search string.
-
The optional escape character is a string literal or character. Use the escape character to interpret the underscore and percent characters of the pattern string as the standard characters.
-
-
IS [NOT] NULL expression
If the specified collection value is null or unknown, the value of the IS [NOT] EMPTY expression becomes unknown.
-
[NOT] MEMBER [OF] expression
If the specified value is null or unknown, the return value becomes unknown.
-
ALL expression or ANY (SOME) expression
-
If the conditional operation is neither true nor false, the value becomes unknown.
-
The comparison operators that can be used together are =, <, <=, >, >=, <>.
-
The sub-query result type must be the same as the comparison operator type. In Cosminexus JPA Provider, an exception occurs if the types are different.
-
SOME is the synonym of ANY. #
# This note is only applicable to ANY (SOME) expression.
-
-
Sub-query
Used in the WHERE clause and HAVING clause. Cannot be used in the FROM clause.
(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.
|
Category |
Functions |
Return value |
Supplement to arguments |
|---|---|---|---|
|
String function |
CONCAT |
Concatenated string of the argument |
-- |
|
SUBSTRING |
String with the start position and length specified in the argument |
The 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. |
|
|
TRIM |
String with a specific character removed |
If 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. |
|
|
LOWER |
De-capitalized string |
-- |
|
|
UPPER |
Capitalized string |
-- |
|
|
LENGTH |
Integer value of the string length |
-- |
|
|
LOCATE |
Integer 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 function |
ABS |
Absolute value with the same type as the function argument (Integer, Float, or Double) |
A numerical value is passed as an argument. |
|
SQRT |
Square root of the numerical value passed in the argument (real number) |
A numerical value is passed as an argument. |
|
|
MOD |
Remainder of two numerical values passed in the argument (integer) |
Two integers are passed. |
|
|
SIZE |
Number of collection elements (integer) |
Collection is passed. |
|
|
Date and time function |
CURRENT_DATE |
Database date |
-- |
|
CURRENT_TIME |
Database time |
-- |
|
|
CURRENT_TIMESTAMP |
Database 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:
-
Period (.)
-
Arithmetic operators
Unary operation (+, -), multiplication and division (*, /), addition and subtraction (+, -)
-
Comparison operators
=, >, >=, <, <=, <> (not equal), [NOT] BETWEEN, [NOT] LIKE, [NOT] IN, IS [NOT] NULL, IS [NOT] EMPTY, [NOT] MEMBER [OF]
-
Logical operators
NOT, AND, OR
(b) Notes on conditional expressions
-
A conditional expression is made up of a conditional expression, comparison expression, and a logical operator, and the evaluation result is made up of the path expression that forms a boolean value, boolean literal, and input parameters of boolean type.
-
An arithmetic expression is available in the comparison expression. An arithmetic expression is made up of other arithmetic expressions and the four arithmetic operations. The result is made up of the path expression that forms a numerical value, number literals, and the input parameters of the numerical type.
-
An arithmetic operation uses numeric promotion.
-
To specify the evaluation order of the expression, you can enclose the evaluation order in parentheses.
-
The set function is only available in the conditional expression of the HAVING clause.
-
In a conditional expression, do not use fields mapped as a serialized format or lobs.
(c) Notes on using literals
-
Enclose a character literal in single quotes (example: 'literal'). For a string literal containing single quotes, use two single quotes.
-
As in the case of the JavaString literal, use Unicode character encoding for the query string literal.
-
The use of Java escape expression is not supported in the query string literal.
-
For the enum literal, you can use the literal of the Java enum literal syntax. The enum literal requires an enum class name.
-
The boolean literal is TRUE and FALSE. This literal is not case sensitive.
(d) Notes on identification variables
-
The identification variable is an identifier declared in the FROM clause of the query, so the identification variable cannot be declared in another clause. If the variable is declared in a clause other than the FROM clause, an exception occurs. For all the identification variables used in the WHERE or HAVING clause of the SELECT statement or DELETE statement, use the identification variables defined in the FROM clause.
-
The range of the identification variables is determined by the WHERE clause and HAVING clause. An identification variable can specify the collection members and instances of the abstract schema type of the entity, but cannot specify the collection itself. If a collection is specified, an exception occurs.
(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
-
The input parameters are available in the WHERE clause or HAVING clause of the query.
-
Do not mix the location parameters and named parameters in one query. If the parameters are mixed, the operations might not function properly.
-
If the value of the input parameters is null, the value returned by the comparison operation or arithmetic operation containing the input parameters becomes unknown.
For details on how to use the location parameters and the named parameters, see 9.16.1(2) How to specify the parameters.