SQL Operators

Operator is a reserved word or a character used primarily in an SQL statement WHERE clause to perform operations, such as comparisons and arithmetic operations.
 In another word operators are used inside an expression or condition to specify particular operations.SQL operator can be divided into main six categories :

1. Arithmetic Operators
2. Relational Operators
3. Logical Operators
4. Range searching Operators
5. Set Searching Operators
6. Character Operators

Arithmetic Operators:

Arithmetic operators can perform arithmetical operation on numeric operands involved. Arithmetic operators such as addition(+), subtraction(-), multiplication(*) and division(/). The + and - operators can also be used in date arithmetic.

Operator	Description
+	Addition - Adds values on either side of the operator
-	Subtraction - Subtracts right hand operand from left hand operand
*	Multiplication - Multiplies values on either side of the operator
/	Division - Divides left hand operand by right hand operand
%	Modulus - Divides left hand operand by right hand operand and returns remainder

Example:

Relational Operators:

Relational operators are used in condition to compare one expression with other. The Relation operators are =, <, >, >=, <=, !=. These realtion operators compare expressions and returns one of three values : True, False, Unknown. An unknown is returned when comparision is performed with a null value. An unknown is treated like as false.

Operator	Description
=	Equals
<	Less than
>	Greater than
<=	Less than or equal to
>=	Greater than or equal to
!= or <>	Not Equals

Example:

Logical Operators:

SQL provides three logical operators : AND, OR and NOT. These operators are explained below:

(i) AND Operator:

• The AND Operator allows creating an SQL statement based on two or more conditions being met.
• It can be used in any valid SQL statement such as select, insert, or delete.

Example:

(ii) OR Operator:

• The OR condition allows creating an SQL statement where records are returned when any one of the conditions are met
• It is used to combine two or more conditions in WHERE and HAVING clause.

Example:

(iii) NOT Operator:

• The Oracle engine will process all rows in a table and display only those records that do not satisfy the condition specified
• It is used to negate the result of any condition or group of conditions.

Example:

Range Searching Operator:

• Between operator is used to check between two values or specific range.

Syntax:

... ColumnName BETWEEN lower limit AND upper limit;

• Selects the rows that contain values within a specified lower and upper limit.
• The lower and upper limit values must be linked with the keyword AND.
• The lower and upper limits are inclusive in range.

Example:

Set Searching Operator:

• The IN operator can be used to select rows that match one of the values in a list.

Syntax:

... ColumnName IN ( value1, value2, ..., valueN );

• Select rows that contain any value given in a set.
• This is similar to '='. But, '=' compares  single value to another single value, while IN compares single value to a list (set) of values provided with IN predicate.
• Can be used when there is a need to use multiple OR conditions.
• Can be used with numerical, Character as well as date data type.  

Example:

Character Operator:

• Character operators can be used to manipulate the way character strings are represented - both in the output of data as well as in providing conditions. The LIKE operator is used for character operator.

Syntax:

... ColumnName like Pattern;

• The like operator is used with special character % and _(underscore).
• This is similar to '='. BUT, the '=' operator compares for exact matching. While LIKE compares for pattern similarity.

Example:

---

• Prev
• Next

---