Oracle Operators

An operator manipulates individual data items and returns a result.

This chapter contains these sections:

• About SQL Operators
• Arithmetic Operators
• Concatenation Operator
• Set Operators
• User-Defined Operators

This chapter discusses nonlogical (non-Boolean) operators. These operators cannot by themselves serve as the condition of a WHERE or HAVING clause in queries or subqueries. For information on logical operators, which serve as conditions, please refer to Chapter 5, "Conditions".

 

About SQL Operators

Operators manipulate individual data items called operands or arguments. Operators are represented by special characters or by keywords. For example, the multiplication operator is represented by an asterisk (*).

Note: If you have installed Oracle Text, you can use the SCORE operator, which is part of that product, in Oracle Text queries. For more information on this operator, please refer to Oracle Text Reference.

Unary and Binary Operators

The two general classes of operators are:

• unary: A unary operator operates on only one operand. A unary operator typically appears with its operand in this format:

  operator operand
  
  

• binary: A binary operator operates on two operands. A binary operator appears with its operands in this format:

  operand1 operator operand2
  
  

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 (||).

Operator Precedence

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.

Table 3-1   SQL Operator Precedence

Operator	Operation
+, - (as unary operators), PRIOR	identity, negation, location in hierarchy
*, /	multiplication, division
+, - (as binary operators), ||	addition, subtraction, concatenation
SQL conditions are evaluated after SQL operators	See "Condition Precedence"

Precedence Example

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.

See Also: "Set Operators" "Hierarchical Queries" for information on the PRIOR operator, which is used only in hierarchical queries

Set Operators

Set operators combine the results of two component queries into a single result. Queries containing set operators are called compound queries. Table 3-4 lists SQL set operators. They are fully described, including restrictions on these operators, in "The UNION [ALL], INTERSECT, MINUS Operators".

Table 3-4  Set Operators

Operator	Returns
UNION	All rows selected by either query
UNION ALL	All rows selected by either query, including all duplicates
INTERSECT	All distinct rows selected by both queries
MINUS	All distinct rows selected by the first query but not the second

 

 

Arithmetic Operators

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.

Table 3-2 Arithmetic Operators

Operator	Purpose	Example
+ -	When these denote a positive or negative expression, they are unary operators.	SELECT * FROM order_items WHERE quantity = -1; SELECT * FROM employees WHERE -salary < 0;
	When they add or subtract, they are binary operators.	SELECT hire_date FROM employees WHERE SYSDATE - hire_date > 365;
* /	Multiply, divide. These are binary operators.	UPDATE employees SET salary = salary * 1.1;

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.

See Also: "Comments" for more information on comments within SQL statements

 

User-Defined Operators

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. They reside in the same namespace as tables, views, types, and standalone 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 includes, which takes as input a text column and a keyword and returns 1 if the row contains the specified keyword, you can then write the following SQL query:

SELECT * FROM product_descriptions 
   WHERE includes (translated_description, 'Oracle and UNIX') = 1;

See Also: CREATE OPERATOR and Oracle9i Data Cartridge Developer's Guide for more information on user-defined operators

 

Concatenation Operator

The concatenation operator manipulates character strings and CLOB data. Table 3-3 describes the concatenation operator.

Table 3-3 Concatenation Operator

Operator	Purpose	Example
||	Concatenates character strings and CLOB data.	SELECT 'Name is ' || last_name FROM employees;

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. If either argument is a CLOB, the result is a temporary CLOB. Trailing blanks in character strings are preserved by concatenation, regardless of the datatypes of the string or CLOB.

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.

See Also: "Character Datatypes" for more information on the differences between the CHAR and VARCHAR2 datatypes Oracle9i Application Developer's Guide - Large Objects (LOBs) for more information about CLOBs The functions CONCAT and NVL

Example

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) );

INSERT INTO tab1 (col1,  col2,     col3,     col4)
        VALUES   ('abc', 'def   ', 'ghi   ', 'jkl');

SELECT col1||col2||col3||col4 "Concatenation"
        FROM tab1;

Concatenation
------------------------
abcdef   ghi   jkl