Queries with Other Conditional Expressions

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.

The LIKE Expression

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

The IS NULL Expression

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

The IS EMPTY Expression

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

The BETWEEN Expression

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

Comparison Operators

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