Oracle8i SQL Reference Release 8.1.5 A67779-01 |
|
An operator manipulates individual data items and returns a result. The data items are called operands or arguments. Operators are represented by special characters or by keywords. For example, the multiplication operator is represented by an asterisk (*) and the operator that tests for nulls is represented by the keywords IS NULL.
This chapter discusses the following topics:
The two general classes of operators are:
Other operators with special formats accept more than two operands. If an operator is given a null operand, the result is always null. The only operator that does not follow this rule is concatenation (||).
Precedence is the order in which Oracle evaluates different operators in the same expression. When evaluating an expression containing multiple operators, Oracle evaluates operators with higher precedence before evaluating those with lower precedence. Oracle evaluates operators with equal precedence from left to right within an expression.
Table 3-1 lists the levels of precedence among SQL operators from high to low. Operators listed on the same line have the same precedence.
In the following expression, multiplication has a higher precedence than addition, so Oracle first multiplies 2 by 3 and then adds the result to 1.
1+2*3
You can use parentheses in an expression to override operator precedence. Oracle evaluates expressions inside parentheses before evaluating those outside.
SQL also supports set operators (UNION, UNION ALL, INTERSECT, and MINUS), which combine sets of rows returned by queries, rather than individual data items. All set operators have equal precedence.
You can use an arithmetic operator in an expression to negate, add, subtract, multiply, and divide numeric values. The result of the operation is also a numeric value. Some of these operators are also used in date arithmetic. Table 3-2 lists arithmetic operators.
Do not use two consecutive minus signs (--) in arithmetic expressions to indicate double negation or the subtraction of a negative value. The characters -- are used to begin comments within SQL statements. You should separate consecutive minus signs with a space or a parenthesis. For more information on comments within SQL statements, see "Comments".
The concatenation operator manipulates character strings. Table 3-3 describes the concatenation operator.
Operator | Purpose | Example |
---|---|---|
|| |
Concatenates character strings. |
SELECT 'Name is ' || ename FROM emp; |
The result of concatenating two character strings is another character string. If both character strings are of datatype CHAR, the result has datatype CHAR and is limited to 2000 characters. If either string is of datatype VARCHAR2, the result has datatype VARCHAR2 and is limited to 4000 characters. Trailing blanks in character strings are preserved by concatenation, regardless of the strings' datatypes. For more information on the differences between the CHAR and VARCHAR2 datatypes, see "Character Datatypes".
On most platforms, the concatenation operator is two solid vertical bars, as shown in Table 3-3. However, some IBM platforms use broken vertical bars for this operator. When moving SQL script files between systems having different character sets, such as between ASCII and EBCDIC, vertical bars might not be translated into the vertical bar required by the target Oracle environment. Oracle provides the CONCAT character function as an alternative to the vertical bar operator for cases when it is difficult or impossible to control translation performed by operating system or network utilities. Use this function in applications that will be moved between environments with differing character sets.
Although Oracle treats zero-length character strings as nulls, concatenating a zero-length character string with another operand always results in the other operand, so null can result only from the concatenation of two null strings. However, this may not continue to be true in future versions of Oracle. To concatenate an expression that might be null, use the NVL function to explicitly convert the expression to a zero-length string.
This example creates a table with both CHAR and VARCHAR2 columns, inserts values both with and without trailing blanks, and then selects these values and concatenates them. Note that for both CHAR and VARCHAR2 columns, the trailing blanks are preserved.
CREATE TABLE tab1 (col1 VARCHAR2(6), col2 CHAR(6), col3 VARCHAR2(6), col4 CHAR(6) ); Table created. INSERT INTO tab1 (col1, col2, col3, col4) VALUES ('abc', 'def ', 'ghi ', 'jkl'); 1 row created. SELECT col1||col2||col3||col4 "Concatenation" FROM tab1; Concatenation ------------------------ abcdef ghi jkl
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.
A logical operator 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 3-5 lists logical operators.
For example, in the WHERE clause of the following SELECT statement, the AND logical operator is used to ensure that only those hired before 1984 and earning more than $1000 a month are returned:
SELECT * FROM emp WHERE hiredate < TO_DATE('01-JAN-1984', 'DD-MON-YYYY') AND sal > 1000;
Table 3-6 shows the result of applying the NOT operator to a condition.
|
TRUE |
FALSE |
UNKNOWN |
---|---|---|---|
NOT |
|
|
|
Table 3-7 shows the results of combining two expressions with AND.
AND |
TRUE |
FALSE |
UNKNOWN |
TRUE |
|
|
|
FALSE |
|
|
|
UNKNOWN |
|
|
|
Table 3-8 shows the results of combining two expressions with OR.
OR |
TRUE |
FALSE |
UNKNOWN |
TRUE |
|
|
|
FALSE |
|
|
|
UNKNOWN |
|
|
|
Set operators combine the results of two component queries into a single result. Queries containing set operators are called compound queries. Table 3-9 lists SQL set operators.
All set operators have equal precedence. If a SQL statement contains multiple set operators, Oracle evaluates them from the left to right if no parentheses explicitly specify another order.
The corresponding expressions in the select lists of the component queries of a compound query must match in number and datatype. If component queries select character data, the datatype of the return values are determined as follows:
Consider these two queries and their results:
SELECT part FROM orders_list1; PART ---------- SPARKPLUG FUEL PUMP FUEL PUMP TAILPIPE SELECT part FROM orders_list2; PART ---------- CRANKSHAFT TAILPIPE TAILPIPE
The following examples combine the two query results with each of the set operators.
The following statement combines the results with the UNION operator, which eliminates duplicate selected rows. This statement shows how datatype must match when columns do not exist in one or the other table:
SELECT part, partnum, to_date(null) date_in FROM orders_list1 UNION SELECT part, to_date(null), date_in FROM orders_list2; PART PARTNUM DATE_IN ---------- ------- -------- SPARKPLUG 3323165 SPARKPLUG 10/24/98 FUEL PUMP 3323162 FUEL PUMP 12/24/99 TAILPIPE 1332999 TAILPIPE 01/01/01 CRANKSHAFT 9394991 CRANKSHAFT 09/12/02 SELECT part FROM orders_list1 UNION SELECT part FROM orders_list2; PART ---------- SPARKPLUG FUEL PUMP TAILPIPE CRANKSHAFT
The following statement combines the results with the UNION ALL operator, which does not eliminate duplicate selected rows:
SELECT part FROM orders_list1 UNION ALL SELECT part FROM orders_list2; PART ---------- SPARKPLUG FUEL PUMP FUEL PUMP TAILPIPE CRANKSHAFT TAILPIPE TAILPIPE
Note that the UNION operator returns only distinct rows that appear in either result, while the UNION ALL operator returns all rows. A PART value that appears multiple times in either or both queries (such as 'FUEL PUMP') is returned only once by the UNION operator, but multiple times by the UNION ALL operator.
The following statement combines the results with the INTERSECT operator, which returns only those rows returned by both queries:
SELECT part FROM orders_list1 INTERSECT SELECT part FROM orders_list2; PART ---------- TAILPIPE
The following statement combines results with the MINUS operator, which returns only rows returned by the first query but not by the second:
SELECT part FROM orders_list1 MINUS SELECT part FROM orders_list2; PART ---------- SPARKPLUG FUEL PUMP
Table 3-10 lists other SQL operators.
Operator | Purpose | Example |
---|---|---|
(+) |
Indicates that the preceding column is the outer join column in a join. See "Outer Joins". |
SELECT ename, dname FROM emp, dept WHERE dept.deptno = emp.deptno(+); |
PRIOR |
Evaluates the following expression for the parent row of the current row in a hierarchical, or tree-structured, query. In such a query, you must use this operator in the CONNECT BY clause to define the relationship between parent and child rows. You can also use this operator in other parts of a SELECT statement that performs a hierarchical query. The PRIOR operator is a unary operator and has the same precedence as the unary + and - arithmetic operators. See "Hierarchical Queries". |
SELECT empno, ename, mgr FROM emp CONNECT BY PRIOR empno = mgr; |
Like built-in operators, user-defined operators take a set of operands as input and return a result. However, you create them with the CREATE OPERATOR statement, and they are identified by names (e.g., MERGE). They reside in the same namespace as tables, views, types, and stand-alone functions.
Once you have defined a new operator, you can use it in SQL statements like any other built-in operator. For example, you can use user-defined operators in the select list of a SELECT statement, the condition of a WHERE clause, or in ORDER BY clauses and GROUP BY clauses. However, you must have EXECUTE privilege on the operator to do so, because it is a user-defined object.
For example, if you define an operator CONTAINS, which takes as input a text document and a keyword and returns 1 if the document contains the specified keyword, you can then write the following SQL query:
SELECT * FROM emp WHERE contains (resume, 'Oracle and UNIX') = 1;
For more information on user-defined operators, see "CREATE OPERATOR" and Oracle8i Data Cartridge Developer's Guide .