Every WHERE clause must specify a conditional expression, of which there are several kinds. In the previous examples, the conditional expressions are comparison expressions that test for equality. The following examples demonstrate some of the other kinds of conditional expressions. For descriptions of all conditional expressions, see the section WHERE Clause.
SELECT p FROM Player p WHERE p.name LIKE ’Mich%’
Data retrieved: All players whose names begin with “Mich.”
Description: The LIKE expression uses wildcard characters to search for strings that match the wildcard pattern. In this case, the query uses the LIKE expression and the % wildcard to find all players whose names begin with the string “Mich.” For example, “Michael” and “Michelle” both match the wildcard pattern.
See also: LIKE Expressions
SELECT t FROM Team t WHERE t.league IS NULL
Data retrieved: All teams not associated with a league.
Description: The IS NULL expression can be used to check if a relationship has been set between two entities. In this case, the query checks to see if the teams are associated with any leagues, and returns the teams that do not have a league.
See also: NULL Comparison Expressions, NULL Values
SELECT p FROM Player p WHERE p.teams IS EMPTY
Data retrieved: All players who do not belong to a team.
Description: The teams relationship field of the Player entity is a collection. If a player does not belong to a team, then the teams collection is empty and the conditional expression is TRUE.
See also: Empty Collection Comparison Expressions
SELECT DISTINCT p FROM Player p WHERE p.salary BETWEEN :lowerSalary AND :higherSalary
Data retrieved: The players whose salaries fall within the range of the specified salaries.
Description: This BETWEEN expression has three arithmetic expressions: a persistent field (p.salary) and the two input parameters (:lowerSalary and :higherSalary). The following expression is equivalent to the BETWEEN expression:
p.salary >= :lowerSalary AND p.salary <= :higherSalary
See also: BETWEEN Expressions
SELECT DISTINCT p1 FROM Player p1, Player p2 WHERE p1.salary > p2.salary AND p2.name = :name
Data retrieved: All players whose salaries are higher than the salary of the player with the specified name.
Description: The FROM clause declares two identification variables (p1 and p2) of the same type (Player). Two identification variables are needed because the WHERE clause compares the salary of one player (p2) with that of the other players (p1).
See also: Identification Variables