Conditions

A condition specifies 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 statements in Chapter 7, "SQL Statements".

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 statement:

• WHERE

• START WITH

• CONNECT BY

• HAVING

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

The following simple condition always evaluates to TRUE:

1 = 1 

The following more complex condition 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) 

Here are some valid conditions:

name = '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 

Conditions can have several forms, as shown in the following syntax. The description of each statement in Chapter 7, "SQL Statements", documents the restrictions on the conditions in the statement. The sections that follow describe the various forms of conditions.

condition::=

Simple Comparison Conditions

A simple comparison condition specifies a comparison with expressions or subquery results.

simple_comparison_condition::=

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

Group Comparison Conditions

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

group_comparison_condition::=

See "SELECT and Subqueries".

Membership Conditions

A membership condition tests for membership in a list or subquery.

membership_condition::=

Range Conditions

A range condition tests for inclusion in a range.

range_condition::=

NULL Conditions

A NULL condition tests for nulls.

NULL_condition::=

EXISTS Conditions

An EXISTS condition tests for existence of rows in a subquery.

EXISTS_condition::=

LIKE Conditions

A LIKE condition specifies a test involving pattern matching.

LIKE_condition::=

Compound Conditions

A compound condition specifies a combination of other conditions.

compound_condition::=