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 operator, the result is always null. The only operator that does not follow this rule is concatenation (||).
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.
Highest Precedence
Unary + - arithmetic operators
PRIOR Operator
* / arithmetic operators
Binary = - arithmetic operators
|| character operators
All comparison operators
NOT logical operator
AND logical operator
OR logical operator
Table 3 - 1. SQL Operator Precedence
You can use parentheses in an expression to override operator precedence. Oracle7 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.
Example
In the following expression multiplication has a higher precedence than addition, so Oracle7 first multiplies 2 by 3 and then adds the result to 1.
1+2*3
Operator
Purpose
Example
+ -
Denotes a positive or negative expression. These are unary operators.
SELECT * FROM orders WHERE qtysold = -1 SELECT * FROM emp WHERE -sal < 0
* /
Multiplies, divides. These are binary operators.
UPDATE emp SET sal = sal * 1.1
+ -
Adds, subtracts. These are binary operators.
SELECT sal + comm FROM emp WHERE SYSDATE - hiredate > 365
Table 3 - 2. Arithmetic Operators
Do not use consecutive minus signs with no separation (- -) 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 the section "Comments" ![[*]](jump.gif)
.
Operator
Purpose
Example
||
Concatenates character strings.
SELECT 'Name is ' || ename FROM emp
Table 3 - 3. Character Operators
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 255 characters. If either string is of datatype VARCHAR2, the result has datatype VARCHAR2 and is limited to 2000 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 the section "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 Oracle7 environment. Because it may be difficult or impossible to control translation performed by operating system or network utilities, the CONCAT character function is provided as an alternative to the vertical bar operator. Its use is recommended in applications that will be moved to environments with differing character sets.
Although Oracle7 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 only result from the concatenation of two null strings. However, this may not continue to be true in future versions of Oracle7. To concatenate an expression that might be null, use the NVL function to explicitly convert the expression to a zero-length string.
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, concatenating 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
. Table 3 - 4 lists comparison operators.
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 null, the entire expression results in a null. This behavior can easily be overlooked, especially when the NOT IN operator references a subquery.
where:
char1
is a value to be compared with a pattern. This value can have datatype CHAR or VARCHAR2.
NOT
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
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'.
While 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 can only 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%'. Oracle7 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 usually use special characters that Oracle7 matches with different characters in the value:
UPPER(ename) LIKE 'SM%'
Pattern Matching on Indexed Columns When LIKE is used to search an indexed column for a pattern, Oracle7 can use the index to improve the statement's performance if the leading character in the pattern is not "%" or "_". In this case, Oracle7 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 Oracle7 cannot scan the index.
Example I
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
Since case is significant, ENAME values beginning with "Ma," "ma," and "mA" make the condition FALSE.
Example II
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 Oracle7 interprets this character literally in the pattern, rather than as a special pattern matching character.
Example III
To search for any 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 Oracle7 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 only be TRUE if both operands have the same length.
Example IV
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 Oracle7 blank-pads CHAR values, the value of F is blank-padded to 6 bytes. V is not blank-padded and has length 4. Table 3 - 5 shows conditions that evaluate to TRUE and FALSE.
Operator
Function
Example
NOT
Returns TRUE if the following condition is FALSE. Returns FALSE if it is TRUE. If it is UNKNOWN, it remains UNKNOWN
SELECT * FROM emp WHERE NOT (job IS NULL) SELECT * FROM emp WHERE NOT (sal BETWEEN 1000 AND 2000)
AND
Returns TRUE if both component conditions are TRUE. Returns FALSE if either is FALSE. Otherwise returns UNKNOWN.
SELECT * FROM emp WHERE job = 'CLERK' AND deptno = 10
OR
Returns TRUE if either component condition is TRUE. Returns FALSE if both are FALSE. Otherwise returns UNKNOWN.
SELECT * FROM emp WHERE job = 'CLERK' OR deptno = 10
Table 3 - 5. 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
NOT
TRUE
FALSE
UNKNOWN
FALSE
TRUE
UNKNOWN
Table 3 - 6. NOT Truth Table
AND
TRUE
FALSE
UNKNOWN
TRUE
TRUE
FALSE
UNKNOWN
FALSE
FALSE
FALSE
FALSE
UNKNOWN
UNKNOWN
FALSE
UNKNOWN
Table 3 - 7. AND Truth Table
OR
TRUE
FALSE
UNKNOWN
TRUE
TRUE
TRUE
TRUE
FALSE
TRUE
FALSE
UNKNOWN
UNKNOWN
TRUE
UNKNOWN
UNKNOWN
Table 3 - 8. OR Truth Table
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.
Table 3 - 9. Set Operators
All set operators have equal precedence. If a SQL statement contains multiple set operators, Oracle7 evaluates them from the left to right if no parentheses explicitly specify another order. To comply with emerging SQL standards, a future version of Oracle7 will give the INTERSECT operator greater precedence than the other set operators, so you should use parentheses to explicitly specify order of evaluation in queries that use the INTERSECT operator with other set operators.
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:
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.
UNION Example
The following 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_null(null), date_in FROM orders_list2 PART PARTNUM DATE_IN ---------- ------- -------- SPARKPLUS 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
UNION ALL Example
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.
INTERSECT Example
SELECT part FROM orders_list1 INTERSECT SELECT part FROM orders_list2
PART ---------- TAILPIPE
MINUS Example
SELECT part FROM orders_list1 MINUS SELECT part FROM orders_list2 PART ---------- SPARKPLUG FUEL PUMP
Operator
Purpose
Example
(+)
Indicates that the preceding column is the outer join column in a join. See the section "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 the section "Hierarchical Queries" .
SELECT empno, ename, mgr FROM emp CONNECT BY PRIOR empno = mgr
Table 3 - 10. Other SQL Operators