Comparison Operators

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.

Table 3-4  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 "= ANY".	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.

NOT IN Operator

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.

LIKE Operator

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:

char1	is a value to be compared with a pattern. This value can have datatype CHAR or VARCHAR2.
NOT	logically inverts the result of the condition, returning FALSE if the condition evaluates to TRUE and TRUE if it evaluates to FALSE.
char2	is the pattern to which char1 is compared. The pattern is a value of datatype CHAR or VARCHAR2 and can contain the special pattern matching characters % and _.
ESCAPE	identifies a single character as the escape character. The escape character can be used to cause Oracle to interpret % or _ literally, rather than as a special character. If you wish to search for strings containing an escape character, you must specify this character twice. For example, if the escape character is '/', to search for the string 'client/server', you must specify, 'client//server'.

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:

• 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. Note that the pattern '%' cannot match a null.

Case Sensitivity and Pattern Matching

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%' 

Pattern Matching on Indexed Columns

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.

Example 1

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.

Example 2

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.

ESCAPE Option

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.

Example:

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.

Patterns Without %

If a pattern does not contain the "%" character, the condition can be TRUE only if both operands have the same length.

Example:

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.