SQL Operators

There are two type of Operators, namely Comparison Operators and Logical Operators. These operators are used mainly in the WHERE clause, HAVING clause to filter the data to be selected

Generally there are three types of operators in SQL:

  1. SQL Arithmetic Operators
  2. SQL Comparison Operators
  3. SQL Logical Operators
  4. Operators used to negate conditions

SQL Arithmetic Operators:

Let's assume two variables "a" and "b". Here "a" is valued 60 and "b" valued 100.

Example:
Operators Descriptions Examples
+ It is used to add containing values of both operands a+b will give 160
- It subtracts right hand operand from left hand operand a-b will give -40
* It multiply both operand?s values a*b will give 6000
/ It divides left hand operand by right hand operand b/a
% It divides left hand operand by right hand operand and returns reminder b%a

SQL Comparison Operators:

Comparison operators are used to compare the column data with specific values in a condition.

Comparison Operators are also used along with the SELECT statement to filter data based on specific conditions.

The below table describes each comparison operator.

Comparison Operators Description
= equal to
<>, != is not equal to
< less than
> greater than
>= greater than or equal to
<= less than or equal to

SQL Logical Operators:

This is the list of logical operators used in SQL

Operators Description
ALL The ALL operator is used to compare a value to all values in another value set.
AND The AND operator allows the existence of multiple conditions in an SQL statement's WHERE clause.
ANY The ANY operator is used to compare a value to any applicable value in the list according to the condition.
BETWEEN The BETWEEN operator is used to search for values that are within a set of values, given the minimum value and the maximum value.
EXISTS The EXISTS operator is used to search for the presence of a row in a specified table that meets certain criteria.
IN The IN operator is used to compare a value to a list of literal values that have been specified.
LIKE The LIKE operator is used to compare a value to similar values using wildcard operators.
NOT The NOT operator reverses the meaning of the logical operator with which it is used. Eg: NOT EXISTS, NOT BETWEEN, NOT IN, etc. This is a negate operator.
OR The OR operator is used to combine multiple conditions in an SQL statement's WHERE clause.
IS NULL The NULL operator is used to compare a value with a NULL value.
UNIQUE The UNIQUE operator searches every row of a specified table for uniqueness (no duplicates).

"OR" Logical Operator:

If you want to select rows that satisfy at least one of the given conditions, you can use the logical operator, OR.

For example: if you want to find the names of students who are studying either Maths or Science, the query would be like,


		

		SELECT first_name, last_name, subject 

FROM student_details 

WHERE subject = 'Maths' OR subject = 'Science' 

		

The output would be something like,

first_name last_name Subject
Harish Sharma Maths
Girish Sharma Maths
Mithilesh Kushwaha Maths
Vivek Kumar Science

The following table describes how logical "OR" operator selects a row.

Column1 Satisfied? Column2 Satisfied? Row Selected
Yes Yes Yes
Yes No Yes
No Yes Yes
No No No

"AND" Logical Operator:

If you want to select rows that must satisfy all the given conditions, you can use the logical operator, AND.

For Example: To find the names of the students between the age 10 to 15 years, the query would be like:

SELECT first_name, last_name, age 

FROM student_details 

WHERE age >= 10 AND age <= 15;

The output would be something like:

first_name last_name age
------- ------- -------
Harish Sharma 10
Vivek Kumar 12
Girish Sharma 15

The following table describes how logical "AND" operator selects a row.

Column1 Satisfied? Column2 Satisfied? Row Selected
Yes Yes Yes
Yes No No
No Yes No
No No No

"NOT" Logical Operator:

If you want to find rows that do not satisfy a condition, you can use the logical operator, NOT. NOT results in the reverse of a condition. That is, if a condition is satisfied, then the row is not returned.

For example: If you want to find out the names of the students who do not play football, the query would be like:

SELECT first_name, last_name, games 

FROM student_details 

WHERE NOT games = 'Football' 

The output would be something like:

first_name last_name Games
------- ------- -------
Harish Sharma Cricket
Vivek Kumar Badminton
Girish Sharma Chess

The following table describes how logical "NOT" operator selects a row.

Column1 Satisfied? Column2 Satisfied? Row Selected
Yes No No
No Yes Yes

Nested Logical Operators:

You can use multiple logical operators in an SQL statement. When you combine the logical operators in a SELECT statement, the order in which the statement is processed is

  1. NOT
  2. AND
  3. OR

For example: If you want to select the names of the students who age is between 10 and 15 years, or those who do not play football, the

SELECT statement would be

SELECT first_name, last_name, age, games 

FROM student_details 

WHERE age >= 10 AND age <= 15 

OR NOT games = 'Football'
first_name last_name age games
------ ------ ------ ------
Harish sharma 10 Cricket
Mithilesh Kushwaha 15 Chess

In this case, the filter works as follows:

Condition 1: All the students you do not play football are selected.

Condition 2: All the students whose are aged between 10 and 15 are selected.

Condition 3: Finally the result is, the rows which satisfy atleast one of the above conditions is returned.