SQL Query Operators | Arithmetic, Comparison & Logical Operators

Summary: In this tutorial, We’re going to cover what exactly SQL query operators (SQL Operators) are, before providing a comprehensive list of the different types with full examples for each.

Introduction Of SQL Query Operators

An SQL Query Operators are used mainly in an SQL Server statement’s WHERE clause to perform an operation(s), like comparisons, Arithmetic, & logical operations. this operator is a reserved character, which is used for the purpose of specifying the conditions during an SQL statement and to serve as conjunctions for multiple conditions in a statement.

  • Arithmetic operators
  • Comparison operators
  • Logical operators
  • Operators used to negate conditions

A) SQL Arithmetic Operators

Assume that ‘variable a’ holds 100 and ‘variable b’ holds 200, then −

Operator Description Example
Addition (+) The addition operator will add values on either side of the operator. a + b will give 300
Subtraction (-) The subtraction operator will subtract the right-hand operand from the left-hand operand. a – b will give -100
Multiplication (*) The multiplication operator will multiply values on either side of the operator. a * b will give 20000
Division (/) The division operator will divide the left-hand operand by the right-hand operand. b / a will give 2
Modulus (%) The modulus operator will divide the left-hand operand by the right-hand operand and returns the remainder. b % a will give 0

Example:-


sql query operators: SQL Arithmetic example

B) SQL Comparison Operators

Assume that ‘variable a’ holds 100 and ‘variable b’ holds 200, then −

Operator Description Example
= This operator is used to check the values of both operands are equal or not, if they are equal then returns true. (a = b) is not true
!= This operator is used to check the values of two operands are equal or not, if they aren’t equal then returns true. (a != b) is true
> To check the value of the left operand is greater than the value of the right operand, if this condition becomes true then it returns true. (a > b) is not true.
< This operator is used to check whether the value of the left operand is less than the value of the right operand, if the condition is true then return true. (a < b) is true.
<> To check the values of two operands are equal or not, If they are not equal then, it returns TRUE. (a <> b) is true
>= This operator will be used for the purpose of to checks if the value of the left operand is greater than or equal to the value of the right operand, if the condition becomes true then returns true. (a >= b) is not true.
<= This is used for the purpose of to checks if the value of the left operand is less than or equal to the value of the right operand, if the condition becomes true then returns true. (a <= b) is true.
!< This operator will be used to check if the value of the left operand isn’t less than the value of the right operand, if the condition is true then return true. (a !< b) is false.
!> It will be used to check if the value of the left operand isn’t greater than the value of the right operand, if the condition is true then return true (a !> b) is true.

Demo Table: Students

Let’s see to better understanding the example, Here, i will consider the following given below table to perform various operations.

StudentID FirstName LastName City Age
1 Saumya Tripathi Noida 32
2 Pushpendra Verma Gr. Noida 31
3 Mohit Chauhan Jhasi 28
4 Akanksha Jain Lucknow 29
5 Satendra Singh Kanpur 30

Example:

Example[Use equal to]:

StudentID FirstName LastName City Age
3 Mohit Chauhan Jhasi 28

Example[Use greater than]:

StudentID FirstName LastName City Age
1 Saumya Tripathi Noida 32
2 Pushpendra Verma Gr. Noida 31

Example[Use Less than]:

StudentID FirstName LastName City Age
3 Mohit Chauhan Jhasi 28

Example[Use less than or equal to]:

StudentID FirstName LastName City Age
3 Mohit Chauhan Jhasi 28
4 Akanksha Jain Lucknow 29

Example[Not equal to]:

StudentID FirstName LastName City Age
1 Saumya Tripathi Noida 32
2 Pushpendra Verma Gr. Noida 31
3 Mohit Chauhan Jhasi 28
4 Akanksha Jain Lucknow 29

C) SQL Logical Operators

Here is a list of all the logical operators which are available in SQL Server.

Operator Description
ALL The ALL operator is used for the purpose of comparing a value to all values in another value set.
AND it permits the existence of multiple conditions in an SQL statement’s WHERE clause.
ANY To compare a value to any applicable value within the list as per the condition.
BETWEEN It is used to search for values that are within a set of values, given the minimum value & the maximum value.
EXISTS To search for the existence of a row in a specified table that meets a certain criterion.
IN It is used for the purpose of comparing a value to a list of literal values that have been specified.
LIKE The LIKE operator is used for the purpose of comparing a value to similar values using wildcard operators.
NOT The NOT operator in SQL Server reverses the meaning of the logical operator with which it is used. i.e NOT EXISTS, NOT BETWEEN, & NOT IN, etc. This is a negate operator.
OR The OR operator is used for the purpose of combining multiple conditions in an SQL statement’s WHERE clause.
IS NULL The NULL operator is used for the purpose of comparing a value with a NULL value.
UNIQUE The UNIQUE operator in SQL is used for the searches of every row of a specified table for uniqueness (i.e. no duplicates).

Example:

To see the demonstration, I will consider the Students’ table considered above, to perform the Logical Operators operations.

Example[ANY]

StudentID FirstName LastName City Age
1 Saumya Tripathi Noida 32
2 Pushpendra Verma Gr. Noida 31

Example[BETWEEN & AND]

StudentID FirstName LastName City Age
3 Mohit Chauhan Jhasi 28
4 Akanksha Jain Lucknow 29
5 Satendra Singh Kanpur 30

Example[IN]

StudentID FirstName LastName City Age
3 Mohit Chauhan Jhasi 28
5 Satendra Singh Kanpur 30

SQL Query Operators: Conclusion

In this article, You have learned SQL Query Operators (SQL Operators). Here, I have explained only a few demonstrations. I would say, that go ahead & practice for more examples on the different types of operators to get good practice on writing SQL queries.

About Author

admin