SQL Operators

SQL Operators Definition:

An operator may be a reserved word or a character which is used primarily in an SQL statement's WHERE clause to perform different operation(s), such as comparisons and arithmetic operations etc.

The symbols which are used to perform logical and mathematical operations in SQL are called SQL operators.

Types of Operators in SQL

SQL consists mainly three type of Operators which are ase listed below.

  • SQL Arithmetic Operators
  • SQL Comparison Operators
  • SQL Logical Operators

SQL Arithmetic Operators

Let's Assume two Variables 'variable a' holds 50 and 'variable b' holds 100, then perform arithmetic operators-

Example:

OperatorsDescriptionsExamples
+It is used to add containing values of both operandsa+b = 150
-It subtracts right hand operand from left hand operanda-b = -50
*It multiply both operand's valuesa*b = 5000
/ It divides left hand operand by right hand operand b/a = 2
% It divides left hand operand by right hand operand and returns reminder b%a = 0


SQL Comparison Operators

An operator which perfoms comparison operation over the two to more operands is called Comparison Operators.

OperatorDescriptionExample
=Examine both operands value that are equal or not,if yes condition become true.(a=b) is not true
!=This is used to check the value of both operands equal or not,if not condition become true.(a!=b) is true
< >Examines the operand's value equal or not, if values are not equal condition is true(a<>b) is true
>Examine the left operand value is greater than right Operand, if yes condition becomes true(a>b) is not true
< Examines the left operand value is less than right Operand, if yes condition becomes true(a<b) is true
>= Examines that the value of left operand is greater than or equal to the value of right operand or not,if yes condition become true(a>=b) is not true
<= Examines that the value of left operand is less than or equal to the value of right operand or not, if yes condition becomes true (a<=b) is true
!<Examines that the left operand value is not less than the right operand value (a!<b) is not true
!> Examines that the value of left operand is not greater than the value of right operand (a!>b) is true


Logical Operators

Logical operators in SQL are used to perform logical operations on the given expressions in SQL statements.

Operator Description
ALL TRUE if all of the subquery values meet the condition
AND TRUE if all the conditions separated by AND is TRUE
ANY TRUE if any of the subquery values meet the condition
BETWEEN TRUE if the operand is within the range of comparisons
EXISTS TRUE if the subquery returns one or more records
IN TRUE if the operand is equal to one of a list of expressions
LIKE TRUE if the operand matches a pattern
NOT Displays a record if the condition(s) is NOT TRUE
OR TRUE if any of the conditions separated by OR is TRUE
SOME TRUE if any of the subquery values meet the condition


Miscellaneous Operataros in SQL

SQL Compound Operators

Operator Description
+= Add equals
-= Subtract equals
*= Multiply equals
/= Divide equals
%= Modulo equals
&= Bitwise AND equals
^-= Bitwise exclusive equals
|*= Bitwise OR equals

Bitwise Operators

Operator Description
& Bitwise AND
| Bitwise OR
^ Bitwise exclusive OR