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.
What Are the Types of SQL Operators?
Generally, there are three types of operators that are used in SQL.
- Arithmetic Operators
- Comparison Operators
- 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!
