How do we use operators in SQL?

First, let’s define what is an Operator? 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 different kinds of Operators, they are:

Unary and Binary Operators, Precedence, Arithmetic Operators, Concatenation Operator,
Comparison Operators, Logical Operators, Set Operators, Other Built-In Operators, 
User-Defined Operators.

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

2. Precedence: It 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.

SQL Operator 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.

3. Arithmetic Operators:  We 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.

2 Arithmetic Operators

SELECT * FROM orders

 WHERE qtysold = -1;

SELECT * FROM emp

  WHERE -sal < 0;

UPDATE emp

SET sal = sal * 1.1;

SELECT sal + comm FROM emp

WHERE SYSDATE - hiredate> 365;

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.

4. Concatenation Operator: The concatenation operator manipulates character strings.

Concatenation Operator

SELECT 'Name is ' || ename

   FROM emp;

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. Trailing blanks in character strings are preserved by concatenation, regardless of the strings' datatypes.

On most platforms, the concatenation operator is two solid vertical bars. 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.

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

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

5. Comparison Operators: Comparison operators compare one expression with another. The result of such a comparison can be TRUE, FALSE, or UNKNOWN.

Comparison Operators

=

SELECT *

FROM emp

WHERE sal = 1500;

!=

^=

<   >

¬=

SELECT *

FROM emp

WHERE sal != 1500;

>

<

SELECT * FROM emp

WHERE sal > 1500;

SELECT * FROM emp

WHERE sal < 1500;

>=

<= 

SELECT * FROM emp

WHERE sal >= 1500;

SELECT * FROM emp

WHERE sal <= 1500; 

IN

SELECT * FROM emp

WHERE job IN

('CLERK','ANALYST');

SELECT * FROM emp

WHERE sal IN

(SELECT sal FROM emp

WHERE deptno = 30); 

NOT IN 

SELECT * FROM emp

WHERE sal NOT IN

(SELECT sal FROM emp

WHERE deptno = 30);

SELECT * FROM emp

WHERE job NOT IN

('CLERK', ANALYST');

ANY

SOME 

SELECT * FROM emp

WHERE sal = ANY

(SELECT sal FROM emp

WHERE deptno = 30);

ALL 

SELECT * FROM emp

WHERE sal >=

ALL ( 1400, 3000);

[NOT] 

BETWEEN x 

AND y

SELECT * FROM emp

WHERE sal

BETWEEN 2000 AND 3000;

EXISTS 

SELECT ename, deptno 

FROM dept

WHERE EXISTS

(SELECT * FROM emp

WHERE dept.deptno 

= emp.deptno); 

x [NOT] 

LIKE y 

[ESCAPE 'z']

SELECT * FROM tab1

WHERE col1 LIKE

'A_C/%E%' ESCAPE '/';

IS [NOT] 

NULL 

SELECT ename, deptno

FROM emp

WHERE comm IS NULL; 

6. Logical Operators:  A logical operator combines the results of two component conditions to produce a single result based on them or to invert the result of a single condition.

 Logical Operators

NOT 

SELECT *

  FROM emp

  WHERE NOT (job IS NULL);

SELECT *

  FROM emp

  WHERE NOT 

  (sal BETWEEN 1000 AND 2000);

AND 

SELECT *

  FROM emp

  WHERE job = 'CLERK'

  AND deptno = 10;

OR 

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

7. Set Operators: Set operators combine the results of two component queries into a single result. Queries containing set operators are called compound queries.

Set Operators

All set operators have equal precedence. If a SQL statement contains multiple set operators, Oracle evaluates them from the left to right if no parentheses explicitly specify another order. The corresponding expressions in the select lists of the component queries of a compound query must match in number and data type. If component queries select character data, the data type of the return values are determined as follows:

• If both queries select values of data type CHAR, the returned values have data type CHAR.
• If either or both of the queries select values of data type VARCHAR2, the returned values have data type VARCHAR2.
  Examples

Consider these two queries and their results:

SELECT part

    FROM orders_list1;

PART 

---------- 

SPARKPLUG 

FUEL PUMP 

FUEL PUMP 

TAILPIPE 

SELECT part 

    FROM orders_list2;

PART 

---------- 

CRANKSHAFT 

TAILPIPE 

TAILPIPE 

CRANKSHAFT 

8. User-Defined 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 (e.g., MERGE). They reside in the same namespace as tables, views, types, and stand-alone 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.

Example, if you define an operator CONTAINS, which takes as input a text document and a keyword and returns 1 if the document contains the specified keyword, you can then write the following SQL query:

SELECT * FROM emp WHERE contains (resume, 'Oracle and UNIX') = 1;