An Operator is a system defined keyword or a special character or a special sign. It is used to perform a specific operation on data items and finally returns a result. These data items are known as operand or arguments. The Operator can work on one or two operands. Operator can be used in SQL WHERE clause to specify conditions and also can perform arithmetic operations.
z = x + y
Here x, y, z are operands and +, = are operators.
There are two kinds of operator:
- Unary Operator: The Unary operator works on single operand.
- Binary Operator: The Binary operator works on two operands.
The operators have been grouped into several categories depending on operation type. Those are listed below:
1) Arithmetic Operator:
Arithmetic operators are used to perform arithmetical calculation on numeric operands. Some arithmetic operators, (+) and (-) are also used to in date time calculation.
The arithmetic operators are –
|+||Addition||Used to add two or more numbers.|
|–||Subtraction||Used to subtract from one number to another.|
|*||Multiplication||Used to multiply two or more numbers.|
|/||Division||Used to divide one number by another.|
|%||Module||It will return remainder of a division.|
2) Comparison Operator:
Comparison Operators are used to compare one expression with another expression by conditions.
|=||Equal||Used to equality test between two operands.|
|>||Greater Than||Used to compare whether one operand is greater than another operand.|
|>=||Greater Than Equal||Used to compare whether one operand is greater than or equal to another operand.|
|<||Less Than||Used to compare whether one operand is less than another operand.|
|<=||Less Than Equal||Used to compare whether one operand is less than or equal to another operand.|
|!=, <>, ^=||Not Equal||Used to inequality test between two operands.|
|IN||IN||The IN operator will compare and match values in a list.|
|NOT IN||NOT IN||It will negate the IN () condition.|
|BETWEEN||BETWEEN||It will match values within a range.|
|NOT BETWEEN||NOT BETWEEN||It will negate the BETWEEN range.|
|LIKE||LIKE||It will compare and match with a pattern.|
|NOT LIKE||NOT LIKE||It will negate the LIKE operator.|
|IS NULL||IS NULL||Used to test null values.|
|IS NOT NULL||It will return false if the value is null. Otherwise return true.|
|EXISTS||It will return true if the Subquery results single or multiple rows. Otherwise false.|
|NOT EXISTS||It will negate the EXISTS. It will return true when a Subquery will not return any value.|
3) Logical Operator:
The Logical Operators are used to combine two or more than one conditions. The logical operators are AND, OR, NOT.
|AND||The AND operator combines multiple conditions and returns TRUE if all conditions are satisfied.
Suppose you have combined five conditions with AND operator and four conditions have been successfully satisfied but one condition has been FALSE. In this situation AND operator will return FALSE. So, to be TRUE in AND operator, every condition combined by AND operator should be satisfied.
|OR||The OR operator combines multiple conditions and returns TRUE if at least one condition is satisfied.
Suppose you have combined five conditions with OR operator and four conditions have been FALSE but one condition has been TRUE. In this situation, OR operator will return TRUE. So, to be TRUE in OR operator, at least one condition combined by OR operator should be satisfied.
|NOT||The NOT operator negates a condition. If a condition is TRUE then the NOT operator will return FALSE. So, if you want to query those rows that will not satisfy the condition, in this situation you can use NOT operator before the condition.|
4) Set Operator:
The SET operators are used to combine or merge the results returned by multiple queries into a single result set. The SET Operators are UNION, UNION ALL, INTERSECT, INTERSECT ALL and MINUS.
5) Character Operator:
The Character operator works on character strings. The character operator is Concatenation operator. We express Concatenation operator by the special sign ||. This concatenation operator is used to concatenate multiple character or strings.