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

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 D 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.
  

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

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

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

• 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 6.16.1(2) How to specify the parameters.