学步园

Conditions

A condition specifies a combination of one or more expressions and logical (Boolean) operators and returns a value of TRUE, FALSE, or unknown

This chapter contains the following sections:

• About SQL Conditions
• Comparison Conditions
• Logical Conditions
• Membership Conditions
• Range Conditions
• Null Conditions
• EQUALS_PATH
• EXISTS Conditions
• LIKE Conditions
• IS OF type Conditions
• UNDER_PATH
• Compound Conditions

About SQL Conditions

Conditions can have several forms, as shown in the following syntax.

condition::=

Text description of condition

The sections that follow describe the various forms of conditions. You must use appropriate condition syntax whenever condition appears in 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(salary, 0) + NVL(salary + (salary*commission_pct, 0) > 25000)

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

(1 = 1) AND (5 < 7) 

Here are some valid conditions:

name = 'SMITH' 
employees.department_id = departments.department_id 
hire_date > '01-JAN-88' 
job_id IN ('SA_MAN', 'SA_REP') 
salary BETWEEN 5000 AND 10000
commission_pct IS NULL AND salary = 2100

Condition Precedence

Precedence is the order in which Oracle evaluates different conditions in the same expression. When evaluating an expression containing multiple conditions, Oracle evaluates conditions with higher precedence before evaluating those with lower precedence. Oracle evaluates conditions with equal precedence from left to right within an expression.

Table 5-1 lists the levels of precedence among SQL condition from high to low. Conditions listed on the same line have the same precedence. As the table indicates, Oracle evaluates operators before conditions.

Table 5-1 SQL Condition Precedence  

Comparison Conditions

Comparison conditions compare one expression with another. The result of such a comparison can be TRUE, FALSE, or UNKNOWN.

Table 5-2 lists comparison conditions.

Table 5-2   Comparison Conditions

=

SELECT *
  FROM employees
  WHERE salary = 2500;

!=
^=
<   >
¬=

SELECT *
  FROM employees
  WHERE salary != 2500;

>

<

SELECT * FROM employees
  WHERE salary > 2500;
SELECT * FROM employees
  WHERE salary < 2500;

>=

<= 

SELECT * FROM employees
  WHERE salary >= 2500;
SELECT * FROM employees
  WHERE salary <= 2500;

ANY
SOME 

SELECT * FROM employees
  WHERE salary = ANY
  (SELECT salary 
   FROM employees
  WHERE department_id = 30);

ALL 

SELECT * FROM employees
  WHERE salary >=
  ALL ( 1400, 3000);

Simple Comparison Conditions

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

simple_comparison_condition::=

Text description of simple_comparison_condition

expression_list::=

Text description 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 the expression_list, and the values returned by the subquery must match in number and datatype the expressions in expression_list.

Group Comparison Conditions

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

group_comparison_condition::=

Text description of group_comparison_condition

expression_list::=

Text description of 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.

Logical Conditions

A logical condition combines the results of two component conditions to produce a single result based on them or to invert the result of a single condition. Table 5-3 lists logical conditions.

Table 5-3  Logical Conditions  

NOT 

SELECT *
  FROM employees
  WHERE NOT (job_id IS NULL);
SELECT *
  FROM employees
  WHERE NOT 
  (salary BETWEEN 1000 AND 2000);

AND 

SELECT *
  FROM employees
  WHERE job_id = 'PU_CLERK'
  AND department_id = 30;

OR 

SELECT *
  FROM employees
  WHERE job_id = 'PU_CLERK'
  OR department_id = 10;

Table 5-4 shows the result of applying the NOT condition to an expression.

Table 5-4 NOT Truth Table

Table 5-5 shows the results of combining the AND condition to two expressions.

Table 5-5 AND Truth Table

For example, in the WHERE clause of the following SELECT statement, the AND logical condition is used to ensure that only those hired before 1984 and earning more than $1000 a month are returned:

SELECT * FROM employees
WHERE hire_date < TO_DATE('01-JAN-1989', 'DD-MON-YYYY')
   AND salary > 2500;

Table 5-6 shows the results of applying OR to two expressions.

Table 5-6 OR Truth Table

For example, the following query returns employees who have a 40% commission rate or a salary greater than $20,000:

SELECT employee_id FROM employees
   WHERE commission_pct = .4 OR salary > 20000;

Membership Conditions
A membership condition tests for membership in a list or subquery.
membership_condition::=
 
Text description of membership_condition

expression_list::=
 
Text description of 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.
See Also: 
"Expression Lists"
Table 5-7 lists the membership conditions.
Table 5-7  Membership Conditions

IN

SELECT * FROM employees
  WHERE job_id IN
  ('PU_CLERK','SH_CLERK');
SELECT * FROM employees
  WHERE salary IN
  (SELECT salary 
   FROM employees
   WHERE department_id =30);

NOT IN 

SELECT * FROM employees
  WHERE salary NOT IN
  (SELECT salary 
   FROM employees
  WHERE department_id = 30);
SELECT * FROM employees
  WHERE job_id NOT IN
  ('PU_CLERK', 'SH_CLERK');

If any item in the list following a NOT IN operation evaluates to null, then all rows evaluate to FALSE or UNKNOWN, and no rows are returned. For example, the following statement returns the string 'TRUE' for each row:

SELECT 'True' FROM employees
   WHERE department_id NOT IN (10, 20);

However, the following statement returns no rows:

SELECT 'True' FROM employees
    WHERE department_id NOT IN (10, 20, NULL); 

The preceding example returns no rows because the WHERE clause condition evaluates to:

department_id != 10 AND department_id != 20 AND department_id != null 

Because the third condition compares department_id with a null, it results in an UNKNOWN, so the entire expression results in FALSE (for rows with department_id equal to 10 or 20). This behavior can easily be overlooked, especially when the NOT IN operator references a subquery.

Moreover, if a NOT IN condition references a subquery that returns no rows at all, then all rows will be returned, as shown in the following example:

SELECT 'True' FROM employees
   WHERE department_id NOT IN (SELECT 0 FROM dual WHERE 1=2);

Restriction on LEVEL in WHERE Clauses

In a [NOT] IN condition in a WHERE clause, if the right-hand side of the condition is a subquery, you cannot use LEVEL on the left-hand side of the condition. However, you can specify LEVEL in a subquery of the FROM clause to achieve the same result. For example, the following statement is not valid:

SELECT employee_id, last_name FROM employees
   WHERE (employee_id, LEVEL) 
      IN (SELECT employee_id, 2 FROM employees)
   START WITH employee_id = 2
   CONNECT BY PRIOR employee_id = manager_id;

But the following statement is valid because it encapsulates the query containing the LEVEL information in the FROM clause:

SELECT v.employee_id, v.last_name, v.lev 
   FROM 
      (SELECT employee_id, last_name, LEVEL lev 
      FROM employees v
      START WITH employee_id = 100 
      CONNECT BY PRIOR employee_id = manager_id) v 
   WHERE (v.employee_id, v.lev) IN
      (SELECT employee_id, 2 FROM employees); 

Range Conditions
A range condition tests for inclusion in a range.
range_condition::=
 
Text description of range_condition

Table 5-8 describes the range conditions.
Table 5-8  Range Conditions

[NOT] 
BETWEEN x 
AND y

SELECT * FROM employees
  WHERE salary
  BETWEEN 2000 AND 3000;

Null Conditions
A NULL condition tests for nulls.
null_condition::=
 
Text description of null_condition

Table 5-9 lists the null conditions.
Table 5-9  Null Conditions

IS [NOT] 
NULL 

SELECT last_name
  FROM employees
  WHERE commission_pct
  IS NULL;

EXISTS Conditions
An EXISTS condition tests for existence of rows in a subquery.
exists_condition::=
 
Text description of exists_condition

Table 5-10 shows the EXISTS condition.
Table 5-10  EXISTS Condition

EXISTS 

SELECT department_id
  FROM departments d
  WHERE EXISTS
  (SELECT * FROM employees e
    WHERE d.department_id 
    = e.department_id);

LIKE Conditions
The LIKE conditions specify a test involving pattern matching. Whereas the equality operator (=) exactly matches one character value to another, the LIKE conditions match a portion of one character value to another by searching the first value for the pattern specified by the second. LIKE calculates strings using characters as defined by the input character set. LIKEC uses Unicode complete characters. LIKE2 uses UCS2 codepoints. LIKE4 uses USC4 codepoints.
like_condition::=
 
Text description of like_condition

In this syntax:

• char1 is a character expression, such as a character column, called the search value.
• char2 is a character expression, usually a literal, called the pattern.
• esc_char is a character expression, usually a literal, called the escape character.

If esc_char is not specified, then there is no default escape character. If any of char1, char2, or esc_char is null, then the result is unknown. Otherwise, the escape character, if specified, must be a character string of length 1.

All of the character expressions (char1, char2, and esc_char) can be of any of the datatypes CHAR, VARCHAR2, NCHAR, or NVARCHAR2. If they differ, then Oracle converts all of them to the datatype of char1.

The pattern can contain the special pattern-matching characters:

• % matches any string of any length (including length 0)
• _ matches any single character.

To search for the characters % and _, precede them by the escape character. For example, if the escape character is @, then you can use @% to search for %, and @_ to search for _.

To search for the escape character, repeat it. For example, if @ is the escape character, then you can use @@ to search for @.

In the pattern, the escape character should be followed by one of %, _, or the escape character itself.

Table 5-11 describes the LIKE conditions.

Table 5-11  LIKE Conditions

x [NOT] 
LIKE y 

[ESCAPE 
'z'] 

SELECT last_name 
    FROM employees
    WHERE last_name LIKE '%A/_B%' 
ESCAPE '/'; 

To process the LIKE conditions, Oracle divides the pattern into subpatterns consisting of one or two characters each. The two-character subpatterns begin with the escape character and the other character is %, or _, or the escape character.

Let P1, P2, ..., Pn be these subpatterns. The like condition is true if there is a way to partition the search value into substrings S1, S2, ..., Sn so that for all i between 1 and n:

• If Pi is _, then Si is a single character.
• If Pi is %, then Si is any string.
• If Pi is two characters beginning with an escape character, then Si is the second character of Pi.
• Otherwise, Pi = Si.

With the LIKE conditions, you can compare a value to a pattern rather than to a constant. The pattern must appear after the LIKE keyword. For example, you can issue the following query to find the salaries of all employees with names beginning with 'R':

SELECT salary 
    FROM employees
    WHERE last_name LIKE 'R%';

The following query uses the = operator, rather than the LIKE condition, to find the salaries of all employees with the name 'R%':

SELECT salary 
    FROM employees 
    WHERE last_name = 'R%';

The following query finds the salaries of all employees with the name 'SM%'. Oracle interprets 'SM%' as a text literal, rather than as a pattern, because it precedes the LIKE keyword:

SELECT salary 
    FROM employees 
    WHERE 'SM%' LIKE last_name;

Patterns typically use special characters that Oracle matches with different characters in the value:

• An underscore (_) in the pattern matches exactly one character (as opposed to one byte in a multibyte character set) in the value.
• A percent sign (%) in the pattern can match zero or more characters (as opposed to bytes in a multibyte character set) in the value. The pattern '%' cannot match a null.

Case Sensitivity

Case is significant in all conditions comparing character expressions including the LIKE condition and the equality (=) operators. You can use the UPPER function to perform a case-insensitive match, as in this condition:

UPPER(last_name) LIKE 'SM%' 

Pattern Matching on Indexed Columns

When you use LIKE to search an indexed column for a pattern, Oracle can use the index to improve the statement's performance if the leading character in the pattern is not "%" or "_". In this case, Oracle can scan the index by this leading character. If the first character in the pattern is "%" or "_", then the index cannot improve the query's performance because Oracle cannot scan the index.

General Examples

This condition is true for all last_name values beginning with "Ma":

last_name LIKE 'Ma%' 

All of these last_name values make the condition true:

Mallin, Markle, Marlow, Marvins, Marvis, Matos 

Case is significant, so last_name values beginning with "MA", "ma", and "mA" make the condition false.

Consider this condition:

last_name LIKE 'SMITH_' 

This condition is true for these last_name values:

SMITHE, SMITHY, SMITHS 

This condition is false for 'SMITH', since the special character "_" must match exactly one character of the lastname value.

ESCAPE Clause Example

You can include the actual characters "%" or "_" in the pattern by using the ESCAPE clause, which identifies the escape character. If the escape character appears in the pattern before the character "%" or "_" then Oracle interprets this character literally in the pattern, rather than as a special pattern matching character.

To search for employees with the pattern 'A_B' in their name:

SELECT last_name 
    FROM employees
    WHERE last_name LIKE '%A/_B%' ESCAPE '/';

The ESCAPE clause identifies the backslash (/) as the escape character. In the pattern, the escape character precedes the underscore (_). This causes Oracle to interpret the underscore literally, rather than as a special pattern matching character.

Patterns Without % Example

If a pattern does not contain the "%" character, then the condition can be true only if both operands have the same length. Consider the definition of this table and the values inserted into it:

CREATE TABLE ducks (f CHAR(6), v VARCHAR2(6));
INSERT INTO ducks VALUES ('DUCK', 'DUCK');
SELECT '*'||f||'*' "char",
   '*'||v||'*' "varchar"
   FROM ducks;

char     varchar
-------- --------
*DUCK  * *DUCK*

Because Oracle blank-pads CHAR values, the value of f is blank-padded to 6 bytes. v is not blank-padded and has length 4.

Compound Conditions

A compound condition specifies a combination of other conditions.

compound_condition::=

Text description of compound_condition