Operators are used in SQL to perform comparisons, and also to apply conditions in the query. Often SQL operators are also used to join conditions used in the query.
The following types of SQL operators are present in SQL
- Arithmetic operators
- Logical operators
- Comparison operators
- Operations used for negation
Arithmetic Operators in SQL:
Let us assume variable ‘a’ has value 10 and variable ‘ b’ has value 20. The following are the list of arithmetic operators
1) ‘+’ (Addition): Performing a+b results 30
Example:
Query: Select 20+10 ;
Result: 30
2) ‘-‘ (Subtraction) : Performing b-a results 10
Example:
Query: Select 20-10 ;
Result: 10
3) ‘*’(Multiplication) : Performing a*b results 300
Example:
Query: Select 20*10 ;
Result: 200
4) ‘/’(Division) : Performing b/a results 2
Example:
Query: Select 20/10 ;
Result: 2
5) ‘%’(Modulus): Performing b%a gives 0
Example:
Query: Select 20%10 ;
Result: 0
Comparison SQL Operators:
1) ‘=’ (Checks Equality) : Performing a=b returns 0 i.e. false [ It returns “0” for false condition and 1 for true condition ]
Example:
Query: Select 10=20 ;
Result: 0
2) ‘!=’ (Checks Inequality): Performing a!=b returns 1 i.e. true
Example:
Query: Select 10!=20 ;
Result: 1
3) ‘<>’ (Checks Inequality): Performing a<>b returns 1 i.e. true
Example:
Query: Select 20<>10 ;
Result: 1
4) ‘>’(Check left operand greater than right operand): Performing a>b returns 0 i.e. false
Example:
Query: Select 10>20 ;
Result: 0
5) ‘<’(Check left operand greater than right operand): Performing a<b returns 1 i.e. true
Example:
Query: Select 10<20 ;
Result: 1
6) ‘>=’(Check left operand greater than or equal to right operand): Performing a>=b returns 0 i.e. false
Example:
Query: Select 10>=20 ;
Result: 0
7) ‘<=’(Check left operand greater than right operand): Performing a<=b returns 1 i.e. true
Example:
Query: Select 10<=20 ;
Result: 1
Logical Operators in SQL :
- ALL: Useful in comparing a value to all values in given set
- AND: Useful in combining multiple conditions in the query
- ANY: Useful in comparing any applicable value in the list
- BETWEEN: Useful for searching value between given Maximum and Minimum value
- EXISTS: Searches row in the table that meets given criteria
- IN: Useful in compare value to list of literal values specified
- LIKE: Useful for strings like wildcard operations
- NOT: Used with EXISTS, IN, BETWEEN to get negation effect
- OR: Useful in combining multiple conditions in the query
- IS NULL: Useful in comparing the value with the NULL value
- UNIQUE: Searches every row of a specified table to ensure no duplicates