Oracle7 Server SQL Reference

Condition

Purpose

To specify a combination of one or more expressions and logical operators that evaluates to either TRUE, FALSE, or unknown . You must use this syntax whenever condition appears in SQL commands in Chapter 4 "Commands" of this manual.

Syntax

Conditions can have several forms. The description of each command in Chapter 4 "Commands" of this manual documents the restrictions on the conditions in the command.

Form I

A comparison with expressions or subquery results.

For information on comparison operators, see the section "Comparison Operators" .

Form II

A comparison with any or all members in a list or subquery.

For the syntax of a subquery, see .

Form III

A test for membership in a list or subquery.

Form IV

A test for inclusion in a range.

Form V

A test for nulls.

Form VI

A test for existence of rows in a subquery.

Form VII

A test involving pattern matching.

Form VIII

A combination of other conditions.

Usage Notes

You can use a condition in the WHERE clause of these statements:

DELETE

SELECT

UPDATE

You can use a condition in any of these clauses of the SELECT command:

WHERE

START WITH

CONNECT BY

HAVING

A condition could be said to be of the "logical" datatype, although Oracle7 does not formally support such a datatype.

The following is a simple condition that always evaluates to TRUE:

1 = 1

The following is a more complex condition that adds the SAL value to the COMM value (substituting the value 0 for null) and determines whether the sum is greater than the number constant 2500:

NVL(sal, 0) + NVL(comm, 0) > 2500

Logical operators can combine multiple conditions into a single condition. For example, you can use the AND operator to combine two conditions:

(1 = 1) AND (5 < 7)

For more information on how to evaluate conditions with logical operators, see the section "Logical beginning" .

Examples

ename = 'SMITH'

emp.deptno = dept.deptno

hiredate > '01-JAN-88'

job IN ('PRESIDENT', 'CLERK', 'ANALYST')

sal BETWEEN 500 AND 1000

comm IS NULL AND sal = 2000