Operators

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

A unary operator operates on only one operand.

Syntax

operator operand

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.

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.

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 |

The concatenation operator manipulates character strings. The following table lists the only supported 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 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.

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’. |

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.

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

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.

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