skip to main content
Querying data stores with SQL : Supported SQL and Extensions : SQL Expressions : Operators
  

Try Now
Operators
This section describes the operators that can be used in SQL expressions.

Unary Operator

A unary operator operates on only one operand.
Syntax
operator operand

Binary Operator

A binary operator operates on two operands.
Syntax
operand1 operator operand2
If an operator is given a null operand, the result is always null. The only operator that does not follow this rule is concatenation (||), which always returns a VARCHAR.

Arithmetic Operator

You can use an arithmetic operator in an expression to negate, add, subtract, multiply, and divide numeric values. The result of this operation is also a numeric value. The + and - operators are also supported in date/time fields to allow date arithmetic. The following table lists the supported arithmetic operators.
Table 198. Arithmetic Operators
Operator
Purpose
Example
+ -
Denotes a positive or negative expression. These are unary operators.
SELECT * FROM emp WHERE comm = -1
* /
Multiplies, divides. These are binary operators.
UPDATE emp SET sal = sal + sal * 0.10
+ -
Adds, subtracts. These are binary operators.
SELECT sal + comm FROM emp WHERE empno > 100

Concatenation Operator

The concatenation operator manipulates character strings. The following table lists the only supported concatenation operator.
Table 199. Concatenation Operator
Operator
Purpose
Example
||
Concatenates character strings. The operator always returns a VARCHAR.
SELECT 'Name is' || ename FROM emp
The result of concatenating two character strings is the data type VARCHAR.

Comparison Operator

Comparison operators compare one expression to another. The result of such a comparison can be TRUE, FALSE, or UNKNOWN (if one of the operands is NULL). The Hybrid Data Pipeline driver considers the UNKNOWN result as FALSE.
The following table lists the supported comparison operators.
Table 200. Comparison Operators
Operator
Purpose
Example
=
Equality test.
SELECT * FROM emp WHERE sal = 1500
!= or <>
Inequality test.
SELECT * FROM emp WHERE sal != 1500
> and <
“Greater than" and "less than" tests.
SELECT * FROM emp WHERE sal > 1500
SELECT * FROM emp WHERE sal < 1500
>= and <=
“Greater than or equal to" and "less than or equal to" tests.
SELECT * FROM emp WHERE sal >= 1500
SELECT * FROM emp WHERE sal <= 1500
[NOT] IN
“Equal to any member of" test.
SELECT * FROM emp WHERE job IN ('CLERK','ANALYST')
SELECT * FROM emp WHERE sal IN (SELECT sal FROM emp WHERE deptno = 30)
[NOT] BETWEEN x AND y
"Greater than or equal to x" and "less than or equal to y."
SELECT * FROM emp WHERE sal BETWEEN 2000 AND 3000
EXISTS
Tests for existence of rows in a subquery.
SELECT empno, ename, deptno FROM emp e WHERE EXISTS (SELECT deptno FROM dept WHERE e.deptno = dept.deptno)
IS [NOT] NULL
Tests whether the value of the column or expression is NULL.
SELECT * FROM emp WHERE ename IS NOT NULL
SELECT * FROM emp WHERE ename IS NULL
ESCAPE clause in LIKE operator LIKE ’pattern string’ ESCAPE ’c’
The Escape clause is supported in the LIKE predicate to indicate the escape character. Escape characters are used in the pattern string to indicate that any wildcard character that is after the escape character in the pattern string should be treated as a regular character. The default escape character is backslash (\).
SELECT * FROM emp WHERE ENAME LIKE 'J%\_%' ESCAPE '\' This matches all records with names that start with letter 'J' and have the '_' character in them.SELECT * FROM emp WHERE ENAME LIKE 'JOE\_JOHN' ESCAPE '\'This matches only records with name ’JOE_JOHN’.

Logical Operator

A logical operator combines the results of two component conditions to produce a single result or to invert the result of a single condition. The following table lists the supported logical operators.
Table 201. Logical Operators
Operator
Purpose
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
Example
In the Where clause of the following Select statement, the AND logical operator is used to ensure that managers earning more than $1000 a month are returned in the result:
SELECT * FROM emp WHERE jobtitle = manager AND sal > 1000

Operator Precedence

As expressions become more complex, the order in which the expressions are evaluated becomes important. The following table shows the order in which the operators are evaluated. The operators in the first line are evaluated first, then those in the second line, and so on. Operators in the same line are evaluated left to right in the expression. You can change the order of precedence by using parentheses. Enclosing expressions in parentheses forces them to be evaluated together.
Table 202. Operator Precedence
Precedence
Operator
1
+ (Positive), - (Negative)
2
*(Multiply), / (Division)
3
+ (Add), - (Subtract)
4
|| (Concatenate)
5
=, >, <, >=, <=, <>, != (Comparison operators)
6
NOT, IN, LIKE
7
AND
8
OR
Example A
The query in the following example returns employee records for which the department number is 1 or 2 and the salary is greater than $1000:
SELECT * FROM emp WHERE (deptno = 1 OR deptno = 2) AND sal > 1000
Because parenthetical expressions are forced to be evaluated first, the OR operation takes precedence over AND.
Example B
In the following example, the query returns records for all the employees in department 1, but only employees whose salary is greater than $1000 in department 2.
SELECT * FROM emp WHERE deptno = 1 OR deptno = 2 AND sal > 1000
The AND operator takes precedence over OR, so that the search condition in the example is equivalent to the expression deptno = 1 OR (deptno = 2 AND sal > 1000).