Oracle® Database SQL Reference 10g Release 2 (10.2) Part Number B14200-01 |
|
|
View PDF |
Comparison conditions compare one expression with another. The result of such a comparison can be TRUE
, FALSE
, or NULL
.
Large objects (LOBs) are not supported in comparison conditions. However, you can use PL/SQL programs for comparisons on CLOB
data.
When comparing numeric expressions, Oracle uses numeric precedence to determine whether the condition compares NUMBER
, BINARY_FLOAT
, or BINARY_DOUBLE
values. Please refer to "Numeric Precedence" for information on numeric precedence.
Two objects of nonscalar type are comparable if they are of the same named type and there is a one-to-one correspondence between their elements. In addition, nested tables of user-defined object types, even if their elements are comparable, must have MAP
methods defined on them to be used in equality or IN
conditions.
See Also:
|
Table 7-2 lists comparison conditions.
Table 7-2 Comparison Conditions
Type of Condition | Purpose | Example |
---|---|---|
= |
Equality test. |
SELECT * FROM employees WHERE salary = 2500; |
!=
^=
< >
ÿ=
|
Inequality test. Some forms of the inequality condition may be unavailable on some platforms. |
SELECT * FROM employees WHERE salary != 2500; |
> < |
Greater-than and less-than tests. |
SELECT * FROM employees WHERE salary > 2500; SELECT * FROM employees WHERE salary < 2500; |
>= <= |
Greater-than-or-equal-to and less-than-or-equal-to tests. |
SELECT * FROM employees WHERE salary >= 2500; SELECT * FROM employees WHERE salary <= 2500; |
ANY SOME |
Compares a value to each value in a list or returned by a query. Must be preceded by =, !=, >, <, <=, >=. Can be followed by any expression or subquery that returns one or more values.
Evaluates to |
SELECT * FROM employees WHERE salary = ANY (SELECT salary FROM employees WHERE department_id = 30); |
ALL |
Compares a value to every value in a list or returned by a query. Must be preceded by =, !=, >, <, <=, >=. Can be followed by any expression or subquery that returns one or more values.
Evaluates to |
SELECT * FROM employees WHERE salary >= ALL ( 1400, 3000); |
A simple comparison condition specifies a comparison with expressions or subquery results.
simple_comparison_condition::=
expression_list::=
If you use the lower form of this condition (with multiple expressions to the left of the operator), then you must use the lower form of the expression_list
, and the values returned by the subquery must match in number and datatype the expressions in expression_list
.
See Also: "Expression Lists" for more information about combining expressions and SELECT for information about subqueries |
A group comparison condition specifies a comparison with any or all members in a list or subquery.
group_comparison_condition::=
expression_list::=
If you use the upper form of this condition (with a single expression to the left of the operator), then you must use the upper form of expression_list
. If you use the lower form of this condition (with multiple expressions to the left of the operator), then you must use the lower form of expression_list
, and the expressions in each expression_list
must match in number and datatype the expressions to the left of the operator.