**2.1 SQL Operators Overview**

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. There are two general classes of operators: unary and binary. Oracle Lite SQL also supports set operators.

**2.1.1 Unary Operators**

A unary operator uses only one operand. A unary operator typically appears with its operand in the following format:

operator operand

**2.1.2 Binary Operators**

A binary operator uses two operands. A binary operator appears with its operands in the following format:

operand1 operator operand2

**2.1.3 Set Operators**

Set operators combine sets of rows returned by queries, instead of individual data items. All set operators have equal precedence. Oracle Lite supports the following set operators:

- UNION
- UNION ALL
- INTERSECT
- MINUS

The following lists the levels of precedence among the Oracle Lite SQL operators from high to low. Operators listed on the same line have the same level of precedence:

Precedence Level |
SQL Operator |

1 | Unary + – arithmetic operators, PRIOR operator |

2 | * / arithmetic operators |

3 | Binary + – arithmetic operators, || character operators |

4 | All comparison operators |

5 | NOT logical operator |

6 | AND logical operator |

7 | OR logical operator |

### 2.1.4 Other Operators

Other operators with special formats accept more than two operands. If an operator receives a null operator, the result is always null. The only operator that does not follow this rule is CONCAT.

## 2.2 Arithmetic Operators

Arithmetic operators manipulate numeric operands. The – operator is also used in date arithmetic.

**Table 2-2 Arithmetic Operators**

Operator |
Description |
Example |

+ (unary) | Makes operand positive | SELECT +3 FROM DUAL; |

– (unary) | Negates operand | SELECT -4 FROM DUAL; |

/ | Division (numbers and dates) | SELECT SAL / 10 FROM EMP; |

* | Multiplication | SELECT SAL * 5 FROM EMP; |

+ | Addition (numbers and dates) | SELECT SAL + 200 FROM EMP; |

– | Subtraction (numbers and dates) | SELECT SAL – 100 FROM EMP; |

## 2.3 Character Operators

Character operators are used in expressions to manipulate character strings.

**Table 2-3 Character Operators**

Operator |
Description |
Example |

|| | Concatenates character strings | SELECT ‘The Name of the employee is: ‘ || ENAME FROM EMP; |

### 2.3.1 Concatenating Character Strings

With Oracle Lite, you can concatenate character strings with the following results:

- Concatenating two character strings results in another character string.
- Oracle Lite preserves trailing blanks in character strings by concatenation, regardless of the strings’ datatypes.
- Oracle Lite provides the CONCAT character function as an alternative to the vertical bar operator. For example:

· SELECT CONCAT (CONCAT (ENAME, ' is a '),job) FROM EMP WHERE SAL > 2000; ·

This returns:

CONCAT(CONCAT(ENAME ------------------------- KING is a PRESIDENT BLAKE is a MANAGER CLARK is a MANAGER JONES is a MANAGER FORD is a ANALYST SCOTT is a ANALYST 6 rows selected.

- Oracle Lite treats zero-length character strings as nulls. When you concatenate a zero-length character string with another operand the result is always the other operand. A null value can only result from the concatenation of two null strings.

## 2.4 Comparison Operators

Comparison operators are used in conditions that compare one expression with another. The result of a comparison can be TRUE, FALSE, or UNKNOWN.

**Table 2-4 Comparison Operators**

Operator |
Description |
Example |

= | Equality test. | SELECT ENAME “Employee” FROM EMP WHERE SAL = 1500; |

!=, ^=, <> | Inequality test. | SELECT ENAME FROM EMP WHERE SAL ^= 5000; |

> | Greater than test. | SELECT ENAME “Employee”, JOB “Title” FROM EMP WHERE SAL > 3000; |

< | Less than test. | SELECT * FROM PRICE WHERE MINPRICE < 30; |

>= | Greater than or equal to test. | SELECT * FROM PRICE WHERE MINPRICE >= 20; |

<= | Less than or equal to test. | SELECT ENAME FROM EMP WHERE SAL <= 1500; |

IN | “Equivalent to any member of” test. Equivalent to “= ANY”. | SELECT * FROM EMP WHERE ENAME IN (‘SMITH’, ‘WARD’); |

ANY/ SOME | Compares a value to each value in a list or returned by a query. Must be preceded by =, !=, >, <, <=, or >=. Evaluates to FALSE if the query returns no rows. | SELECT * FROM DEPT WHERE LOC = SOME (‘NEW YORK’,’DALLAS’); |

NOT IN | Equivalent to “!= ANY”. Evaluates to FALSE if any member of the set is NULL. | SELECT * FROM DEPT WHERE LOC NOT IN (‘NEW YORK’, ‘DALLAS’); |

ALL | Compares a value with every value in a list or returned by a query. Must be preceded by =, !=, >, <, <=, or >=. Evaluates to TRUE if the query returns no rows. | SELECT * FROM emp WHERE sal >= ALL (1400, 3000); |

[NOT] BETWEENx and y |
[Not] greater than or equal to x and less than or equal to y. |
SELECT ENAME, JOB FROM EMP WHERE SAL BETWEEN 3000 AND 5000; |

EXISTS | TRUE if a sub-query returns at least one row. | SELECT * FROM EMP WHERE EXISTS (SELECT ENAME FROM EMP WHERE MGR IS NULL); |

x [NOT] LIKE y[ESCAPEz] |
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 following ESCAPE is interpretted litteraly, useful when y contains a percent (%) or underscore (_). |
SELECT * FROM EMP WHERE ENAME LIKE ‘%E%’; |

IS [NOT] NULL | Tests for nulls. This is the only operator that should be used to test for nulls. | SELECT * FROM EMP WHERE COMM IS NOT NULL AND SAL > 1500; |

## 2.5 Logical Operators

Logical operators manipulate the results of conditions.

**Table 2-5 Logical Operators**

Operator |
Description |
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 |

## 2.6 Set Operators

Set operators combine the results of two queries into a single result.

**Table 2-6 Set Operators**

Operator |
Description |
Example |

UNION | Returns all distinct rows selected by either query. | SELECT * FROM
(SELECT ENAME FROM EMP WHERE JOB = ‘CLERK’ UNION SELECT ENAME FROM EMP WHERE JOB = ‘ANALYST’); |

UNION ALL | Returns all rows selected by either query, including all duplicates. | SELECT * FROM
(SELECT SAL FROM EMP WHERE JOB = ‘CLERK’ UNION SELECT SAL FROM EMP WHERE JOB = ‘ANALYST’); |

INTERSECT and INTERSECT ALL | Returns all distinct rows selected by both queries. | SELECT * FROM orders_list1
INTERSECT SELECT * FROM orders_list2 |

MINUS | Returns all distinct rows selected by the first query but not the second. | SELECT * FROM (SELECT SAL FROM EMP WHERE JOB = ‘PRESIDENT’
MINUS SELECT SAL FROM EMP WHERE JOB = ‘MANAGER’); |

## 2.7 Other Operators

The following lists other operators:

**Table 2-7 Other Operators**

Operator |
Description |
Example |

(+) | Indicates that the preceding column is the outer join column in a join. | 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 the parent and child rows. | SELECT EMPNO, ENAME, MGR FROM EMP CONNECT BY PRIOR EMPNO = MGR; |