AWS Global Infrastructure

Databases

Topics Covered
  • Cassandra (14 Blogs)
  • MongoDB Dev and Admin (14 Blogs)
  • MySQL (55 Blogs)
  • SQL Essentials Training and Certification (8 Blogs)
SEE MORE

MI-new-launch

myMock Interview Service for Real Tech Jobs

myMock-widget-banner-bg

What are SQL Operators and how do they work?

Published on Oct 14,2019 112 Views
4 / 37 Blog from Introduction to SQL

MI-new-launch

myMock Interview Service for Real Tech Jobs

myMock-mobile-banner-bg

myMock Interview Service for Real Tech Jobs

  • Mock interview in latest tech domains i.e JAVA, AI, DEVOPS,etc
  • Get interviewed by leading tech experts
  • Real time assessment report and video recording

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:

SQL-SQL Operators-Edureka

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

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.

OperatorOperationDescription
+AdditionAdd values on either side of the operator
SubtractionUsed to subtract the right hand side value  from the left hand side value 
MultiplicationMultiples the values present on each side of the operator
/DivisionDivides the left hand side value by the right hand side value
%ModulusDivides 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.

OperatorOperationDescription
=Equal toUsed to check if the values of both operands are equal or not. If they are equal, then it returns TRUE.
>Greater thanReturns TRUE if the value of left operand is greater than the right operand.
<Less thanChecks whether the value of left operand is less than the right operand, if yes returns TRUE.
>=Greater than or equal toUsed 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 toReturns TRUE if the left operand is less than or equal to the right operand.
<> or != Not equal toUsed to check if values of operands are equal or not. If they are not equal then, it returns TRUE.
!>Not greater thanChecks whether the left operand is not greater than the right operand, if yes then returns TRUE.
!<Not less thanReturns 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.

StudentIDFirstNameLastNameAge
1AtulMishra23
2PriyaKapoor21
3RohanSinghania21
4AkankshaJain20
5VaibhavGupta25

Example[Use equal to]:

SELECT * FROM Students
WHERE Age = 20;

Output:

StudentIDFirstNameLastNameAge
4AkankshaJain20

Example[Use greater than]:

SELECT * FROM students
WHERE Age > 23;

Output:

StudentIDFirstNameLastNameAge
5VaibhavGupta25

Example[Use less than or equal to]:

SELECT * FROM students
WHERE Age <= 21;

Output:

StudentIDFirstNameLastNameAge
2PriyaKapoor21
3RohanSinghania21
4AkankshaJain20

Example[Not equal to]:

SELECT * FROM students
WHERE Age > 25;

Output:

StudentIDFirstNameLastNameAge
1AtulMishra23
2PriyaKapoor21
3RohanSinghania21
4AkankshaJain20

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.

OperatorDescription
ALLUsed to compare a specific value to all other values in a set
ANYCompares a specific value to any of the values present in a set.
INUsed to compare a specific value to the literal values mentioned.
BETWEENSearches for values within the range mentioned.
ANDAllows the user to mention multiple conditions in a WHERE clause.
ORCombines multiple conditions in a WHERE clause.
NOTA negate operators, used to reverse the output of the logical operator.
EXISTSUsed to search for the row’s presence in the table.
LIKECompares a pattern using wildcard operators.
SOMESimilar 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:

StudentIDFirstNameLastNameAge
1AtulMishra23
5VaibhavGupta25

Example[BETWEEN & AND]

SELECT * FROM Students
WHERE Age BETWEEN 22 AND 25;

Output:

StudentIDFirstNameLastNameAge
1AtulMishra23

Example[IN]

SELECT * FROM Students
WHERE Age IN('23', '20');

Output:

StudentIDFirstNameLastNameAge
1AtulMishra23
4AkankshaJain20

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. 

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.

Comments
0 Comments

Browse Categories

webinar REGISTER FOR FREE WEBINAR
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP

Subscribe to our Newsletter, and get personalized recommendations.