Oracle8i SQL Reference Release 2 (8.1.6) A76989-01 |
|
Operators, 6 of 10
Comparison operators compare one expression with another. The result of such a comparison can be TRUE
, FALSE
, or UNKNOWN
. For information on conditions, see "Conditions". Table 3-4 lists comparison operators.
Operator | Purpose | Example |
---|---|---|
= |
Equality test. |
SELECT * FROM emp WHERE sal = 1500; |
!= ^= < > ¬= |
Inequality test. Some forms of the inequality operator may be unavailable on some platforms. |
SELECT * FROM emp WHERE sal != 1500; |
> < |
"Greater than" and "less than" tests. |
SELECT * FROM emp WHERE sal > 1500; SELECT * FROM emp WHERE sal < 1500; |
>= <= |
"Greater than or equal to" and "less than or equal to" tests. |
SELECT * FROM emp WHERE sal >= 1500; SELECT * FROM emp WHERE sal <= 1500; |
IN |
"Equal to any member of" test. Equivalent to " |
SELECT * FROM emp WHERE job IN ('CLERK','ANALYST'); SELECT * FROM emp WHERE sal IN (SELECT sal FROM emp WHERE deptno = 30); |
NOT IN |
Equivalent to "!=ALL". Evaluates to FALSE if any member of the set is NULL. |
SELECT * FROM emp WHERE sal NOT IN (SELECT sal FROM emp WHERE deptno = 30); SELECT * FROM emp WHERE job NOT IN ('CLERK', ANALYST'); |
ANY SOME |
Compares a value to each value in a list or returned by a query. Must be preceded by =, !=, >, <, <=, >=. Evaluates to FALSE if the query returns no rows. |
SELECT * FROM emp WHERE sal = ANY (SELECT sal FROM emp WHERE deptno = 30); |
ALL |
Compares a value to every value in a list or returned by a query. Must be preceded by =, !=, >, <, <=, >=. Evaluates to TRUE if the query returns no rows. |
SELECT * FROM emp WHERE sal >= ALL ( 1400, 3000); |
[NOT] BETWEEN x AND y |
[Not] greater than or equal to x and less than or equal to y. |
SELECT * FROM emp WHERE sal BETWEEN 2000 AND 3000; |
EXISTS |
TRUE if a subquery returns at least one row. |
SELECT ename, deptno FROM dept WHERE EXISTS (SELECT * FROM emp WHERE dept.deptno = emp.deptno); |
x [NOT] LIKE y [ESCAPE 'z'] |
TRUE if x does [not] match the pattern y. Within y, the character "%" matches any string of zero or more characters except null. The character "_" matches any single character. Any character, excepting percent (%) and underbar (_) may follow ESCAPE. A wildcard character is treated as a literal if preceded by the character designated as the escape character. |
See "LIKE Operator". SELECT * FROM tab1 WHERE col1 LIKE 'A_C/%E%' ESCAPE '/'; |
IS [NOT] NULL |
Tests for nulls. This is the only operator that you should use to test for nulls. See "Nulls". |
SELECT ename, deptno FROM emp WHERE comm IS NULL; |
Additional information on the NOT
IN
and LIKE
operators appears in the sections that follow.
If any item in the list following a NOT
IN
operation is null, all rows evaluate to UNKNOWN
(and no rows are returned). For example, the following statement returns the string 'TRUE' for each row:
SELECT 'TRUE' FROM emp WHERE deptno NOT IN (5,15);
However, the following statement returns no rows:
SELECT 'TRUE' FROM emp WHERE deptno NOT IN (5,15,null);
The above example returns no rows because the WHERE clause condition evaluates to:
deptno != 5 AND deptno != 15 AND deptno != null
Because all conditions that compare a null result in a null, the entire expression results in a null. This behavior can easily be overlooked, especially when the NOT IN operator references a subquery.
The LIKE
operator is used in character string comparisons with pattern matching. The syntax for a condition using the LIKE
operator is shown in this diagram:
where:
Whereas the equal (=) operator exactly matches one character value to another, the LIKE
operator matches a portion of one character value to another by searching the first value for the pattern specified by the second. Note that blank padding is not used for LIKE
comparisons.
With the LIKE
operator, 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 'SM':
SELECT sal FROM emp WHERE ename LIKE 'SM%';
The following query uses the = operator, rather than the LIKE
operator, to find the salaries of all employees with the name 'SM%':
SELECT sal FROM emp WHERE ename = 'SM%';
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
operator:
SELECT sal FROM emp WHERE 'SM%' LIKE ename;
Patterns typically use special characters that Oracle matches with different characters in the value:
Case is significant in all conditions comparing character expressions including the LIKE
and equality (=) operators. You can use the UPPER
() function to perform a case-insensitive match, as in this condition:
UPPER(ename) LIKE 'SM%'
When LIKE
is used 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 "_", the index cannot improve the query's performance because Oracle cannot scan the index.
This condition is true for all ENAME
values beginning with "MA
":
ename LIKE 'MA%'
All of these ENAME
values make the condition TRUE
:
MARTIN, MA, MARK, MARY
Case is significant, so ENAME
values beginning with "Ma," "ma," and "mA" make the condition FALSE
.
Consider this condition:
ename LIKE 'SMITH_'
This condition is true for these ENAME
values:
SMITHE, SMITHY, SMITHS
This condition is false for 'SMITH', since the special character "_" must match exactly one character of the ENAME
value.
You can include the actual characters "%" or "_" in the pattern by using the ESCAPE
option. The ESCAPE
option 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 ename FROM emp WHERE ename LIKE '%A\_B%' ESCAPE '\';
The ESCAPE
option 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.
If a pattern does not contain the "%" character, 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 freds (f CHAR(6), v VARCHAR2(6)); INSERT INTO freds VALUES ('FRED', 'FRED');
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.
|
![]() Copyright © 1999 Oracle Corporation. All Rights Reserved. |
|