While handling data in databases, we often tend to perform different kinds of operations to manipulate and retrieve data. SQL being the base of database management systems, offers various operators to perform such operations. In this article on SQL operators, I will discuss the various operators used in SQL, in the following sequence:
What are SQL operators?
SQL operators are reserved keywords used in the WHERE clause of a SQL statement to perform arithmetic, logical and comparison operations. Operators act as conjunctions in SQL statements to fulfill multiple conditions in a statement.
Since, there are different types of operators in SQL, let us understand the same in the next section of this article on SQL operators.
Types of SQL Operators
Arithmetic Operators
These operators are used to perform operations such as addition, multiplication, subtraction etc.
| Operator | Operation | Description |
| + | Addition | Add values on either side of the operator |
| – | Subtraction | Used to subtract the right hand side value from the left hand side value |
| * | Multiplication | Multiples the values present on each side of the operator |
| / | Division | Divides the left hand side value by the right hand side value |
| % | Modulus | Divides the left hand side value by the right hand side value; and returns the remainder |
Example:
SELECT 40 + 20; SELECT 40 - 20; SELECT 40 * 20; SELECT 40 / 20; SELECT 40 % 20;
Output:
60 20 800 2 0
Well, that was about the arithmetic operators available in SQL. Next in this article on SQL operators, let us understand the comparison operators available.
Comparison Operators
These operators are used to perform operations such as equal to, greater than, less than etc.
| Operator | Operation | Description |
| = | Equal to | Used to check if the values of both operands are equal or not. If they are equal, then it returns TRUE. |
| > | Greater than | Returns TRUE if the value of left operand is greater than the right operand. |
| < | Less than | Checks whether the value of left operand is less than the right operand, if yes returns TRUE. |
| >= | Greater than or equal to | Used to check if the left operand is greater than or equal to the right operand, and returns TRUE, if the condition is true. |
| <= | Less than or equal to | Returns TRUE if the left operand is less than or equal to the right operand. |
| <> or != | Not equal to | Used to check if values of operands are equal or not. If they are not equal then, it returns TRUE. |
| !> | Not greater than | Checks whether the left operand is not greater than the right operand, if yes then returns TRUE. |
| !< | Not less than | Returns TRUE, if the left operand is not less than the right operand. |
Example:
For your better understanding, I will consider the following table to perform various operations.
| StudentID | FirstName | LastName | Age |
| 1 | Atul | Mishra | 23 |
| 2 | Priya | Kapoor | 21 |
| 3 | Rohan | Singhania | 21 |
| 4 | Akanksha | Jain | 20 |
| 5 | Vaibhav | Gupta | 25 |
Example[Use equal to]:
SELECT * FROM Students WHERE Age = 20;
Output:
| StudentID | FirstName | LastName | Age |
| 4 | Akanksha | Jain | 20 |
Example[Use greater than]:
SELECT * FROM students WHERE Age > 23;
Output:
| StudentID | FirstName | LastName | Age |
| 5 | Vaibhav | Gupta | 25 |
Example[Use less than or equal to]:
SELECT * FROM students WHERE Age <= 21;
Output:
| StudentID | FirstName | LastName | Age |
| 2 | Priya | Kapoor | 21 |
| 3 | Rohan | Singhania | 21 |
| 4 | Akanksha | Jain | 20 |
Example[Not equal to]:
SELECT * FROM students WHERE Age > 25;
Output:
| StudentID | FirstName | LastName | Age |
| 1 | Atul | Mishra | 23 |
| 2 | Priya | Kapoor | 21 |
| 3 | Rohan | Singhania | 21 |
| 4 | Akanksha | Jain | 20 |
Find out our MS SQL Course in Top Cities
| India | India |
| SQL Training in Bangalore | SQL Course in Pune |
| SQL Training in Chennai | SQL Course in Mumbai |
| SQL Training in Hyderabad | SQL Course in Kolkata |
Well, that were few examples on comparison operators. Moving on in this article on SQL operators, let us understand the various logical operators available.
Logical Operators
The logical operators are used to perform operations such as ALL, ANY, NOT, BETWEEN etc.
| Operator | Description |
| ALL | Used to compare a specific value to all other values in a set |
| ANY | Compares a specific value to any of the values present in a set. |
| IN | Used to compare a specific value to the literal values mentioned. |
| BETWEEN | Searches for values within the range mentioned. |
| AND | Allows the user to mention multiple conditions in a WHERE clause. |
| OR | Combines multiple conditions in a WHERE clause. |
| NOT | A negate operators, used to reverse the output of the logical operator. |
| EXISTS | Used to search for the row’s presence in the table. |
| LIKE | Compares a pattern using wildcard operators. |
| SOME | Similar to the ANY operator, and is used compares a specific value to some of the values present in a set. |
Example:
I am going to consider the Students table considered above, to perform a few of the operations.
Example[ANY]
SELECT * FROM Students WHERE Age > ANY (SELECT Age FROM Students WHERE Age > 21);
Output:
| StudentID | FirstName | LastName | Age |
| 1 | Atul | Mishra | 23 |
| 5 | Vaibhav | Gupta | 25 |
Example[BETWEEN & AND]
SELECT * FROM Students WHERE Age BETWEEN 22 AND 25;
Output:
| StudentID | FirstName | LastName | Age |
| 1 | Atul | Mishra | 23 |
Example[IN]
SELECT * FROM Students
WHERE Age IN('23', '20');
Output:
| StudentID | FirstName | LastName | Age |
| 1 | Atul | Mishra | 23 |
| 4 | Akanksha | Jain | 20 |
In this article, I have explained only a few examples. I would say, go forward and practice a few more examples on the different types of operators to get good practice on writing SQL queries.
For details, You can even check out how to manage databases on SQL Server and its concepts with the SQL certification.
If you wish to learn more about MySQL and get to know this open-source relational database, then check out our MySQL DBA Certification Training which comes with instructor-led live training and real-life project experience. This training will help you understand MySQL in-depth and help you achieve mastery over the subject.
Got a question for us? Please mention it in the comments section of this article on “SQL Operators” and I will get back to you.