EXISTS Expressions
EXISTS Expressions
EXISTS
Expressions
The [NOT] EXISTS expression is used with a subquery, and is true only if the result of the
subquery consists of one or more values and is false otherwise.
The following example finds all employees whose spouse is also an employee:
SELECT DISTINCT emp
FROM Employee emp
WHERE EXISTS (
SELECT spouseEmp
FROM Employee spouseEmp
WHERE spouseEmp = emp.spouse)
ALL
and ANY Expressions
The ALL expression is used with a subquery, and is true if all the values returned by the subquery
are true, or if the subquery is empty.
The ANY expression is used with a subquery, and is true if some of the values returned by the
subquery are true. An ANY expression is false if the subquery result is empty, or if all the values
returned are false. The SOME keyword is synonymous with ANY.
The ALL and ANY expressions are used with the =, <, <=, >, >=, <> comparison operators.
The following example finds all employees whose salary is higher than the salary of the
managers in the employee's department:
SELECT emp
FROM Employee emp
WHERE emp.salary > ALL (
SELECT m.salary
FROM Manager m
WHERE m.department = emp.department)
Functional Expressions
The query language includes several string and arithmetic functions which may be used in the
WHERE
or HAVING clause of a query. The functions are listed in the following tables. In
, the start and length arguments are of type int. They designate positions in the
String
argument. The first position in a string is designated by 1. In
argument can be either an int, a float, or a double.
Full Query Language Syntax
The Java EE 5 Tutorial · September 2007
756
