While working with databases, we use SQL queries to manipulate the data and retrieve the desired result. This manipulation of data is achieved through what we call an operator. An operator is a keyword in SQL that helps us to access the data and returns the result based on the operator’s functionality. SQL provides us with many such operators to ease the process of data manipulation. In this article, we will look at what an operator is and then we shall cover different types of operators available to us in SQL.

What Is an SQL operator?

An operator is a reserved word or a character that is used to query our database in a SQL expression. To query a database using operators, we use a WHERE clause. Operators are necessary to define a condition in SQL, as they act as a connector between two or more conditions. The operator manipulates the data and gives the result based on the operator’s functionality.

Full Stack Java Developer Course

In Partnership with HIRIST and HackerEarthEXPLORE COURSE
Full Stack Java Developer Course

What Are the Types of SQL Operators?

Generally, there are three types of operators that are used in SQL.

  1. Arithmetic Operators
  2. Comparison Operators
  3. Logical Operators

Now, let’s look at each one of them in detail.

1. Arithmetic SQL Operators

Arithmetic operators are used to perform arithmetic operations such as addition, subtraction, division, and multiplication. These operators usually accept numeric operands. Different operators that come under this category are given below-

Operator

Operation

Description

+

Addition

Adds operands on either side of the operator

-

Subtraction

Subtracts the right-hand operand from the left-hand operand

*

Multiplication

Multiplies the values on each side

/

Division

Divides left-hand operand by right-hand operand

%

Modulus

Divides left-hand operand by right-hand operand and returns the remainder

2. Comparison SQL Operators

Comparison operators in SQL are used to check the equality of two expressions. It checks whether one expression is identical to another. Comparison operators are generally used in the WHERE clause of a SQL query. The result of a comparison operation may be TRUE, FALSE or UNKNOWN. When one or both the expression is NULL, then the operator returns UNKNOWN. These operators could be used on all types of expressions except expressions that contain a text, ntext or an image. The table below shows different types of comparison operators in SQL:

Operator

Operation

Description

=

Equal to

Checks if both operands have equal value, if yes, then returns TRUE

>

Greater than

Checks if the value of the left-hand operand is greater than the right-hand operand or not

<

Less than

Returns TRUE if the value of the left-hand operand is less than the value of the right-hand operand

>=

Greater than or equal to

It checks if the value of the left-hand operand is greater than or equal to the value of the right-hand operand, if yes, then returns TRUE

<=

Less than or equal to

Examines if the value of the left-hand operator is less than or equal to the right-hand operand

<> or !=

Not equal to

Checks if values on either side of the operator are equal or not. Returns TRUE if values are not equal

!>

Not greater than

Used to check if the left-hand operator’s value is not greater than or equal to the right-hand operator’s value

!<

Not less than

Used to check if the left-hand operator’s value is not less than or equal to the right-hand operator’s value

3. Logical SQL Operators

Logical operators are those operators that take two expressions as operands and return TRUE or False as output. While working with complex SQL statements and queries, comparison operators come in handy and these operators work in the same way as logic gates do. Different logical operations available in SQL are given in the below table.

Operator

Description

ALL

Compares a value to all other values in a set

AND

Returns the records if all the conditions separated by AND are TRUE

ANY

Compares a specific value to any other values in a set

SOME

Compares a value to each value in a set. It is similar to ANY operator

LIKE

It returns the rows for which the operand matches a specific pattern

IN 

Used to compare a value to a specified value in a list

BETWEEN

Returns the rows for which the value lies between the mentioned range

NOT

Used to reverse the output of any logical operator

EXISTS

Used to search a row in a specified table in the database

OR

Returns the records for which any of the conditions separated by OR is true

NULL

Returns the rows where the operand is NULL

Now, that we have learned about what an operator is and its different types such as arithmetic, comparison, and logical operators, you must be curious to see how these actually work in SQL queries. Check out Simplilearn’s SQL certification training and get yourself enrolled for mastering SQL thoroughly, starting now!

About the Author

SimplilearnSimplilearn

Simplilearn is one of the world’s leading providers of online training for Digital Marketing, Cloud Computing, Project Management, Data Science, IT, Software Development, and many other emerging technologies.

View More
  • Disclaimer
  • PMP, PMI, PMBOK, CAPM, PgMP, PfMP, ACP, PBA, RMP, SP, and OPM3 are registered marks of the Project Management Institute, Inc.