Blog_Banner_Asset
    Homebreadcumb forward arrow iconBlogbreadcumb forward arrow iconFull Stack Developmentbreadcumb forward arrow iconSQL Operators & Their Types | SQL Operator Types

SQL Operators & Their Types | SQL Operator Types

Last updated:
10th Mar, 2021
Views
Read Time
10 Mins
share image icon
In this article
Chevron in toc
View All
SQL Operators & Their Types | SQL Operator Types

What are the Operators?

Every database admin or the geeks working on databases uses SQL queries to manipulate the data and retrieve the tables’ results. This manipulation of data is performed with the help of operators. An operator is a keyword used in SQL queries to manipulate the data, and it returns the results based on the functionality of that operator.

These operators allow us to perform multiple functionalities like performing arithmetic operations, comparing two expressions, etc. It generally also acts as a conjunction between multiple conditions in a query. The operators can either be unary operators or binary operators.

Unary operators use only one operand for their execution, whereas Binary operators use two operands. Operands are nothing but the objects on which we can use the operators. For example, consider the following statement: 1 + 2. Here 1 and 2 are the operands, whereas + is the operator. In SQL, instead of using only the numbers, we generally use tables to obtain the results. 

Check out our free courses to get an edge over the competition.

Ads of upGrad blog

In this article, we shall look into the vast range of operators offered in SQL. After listing all the operators, we shall see a few examples for better understanding.

Type of Operators

The operators are categorized based upon their functionality. The types of operators are as follows: – 

Check out upGrad’s Advanced Certification in DevOps

1. Arithmetic Operators

Arithmetic operators are used to performing arithmetic operations like addition, subtraction, division, multiplication, etc. These operators generally take in numeric operands to manipulate the tables. The different operators that come under this category are as follows: – 

OperatorOperationDescription
+AdditionTo add the values on either side of the operator.
SubtractionTo subtract the right-hand operand from the left-hand operand
MultiplicationTo multiply the values on each side of the operator.
/DivisionTo divide the left-hand operand by the right-hand operand.
%ModulusTo divide the left-hand operand by the right-hand operand and return the remainder.

2. Comparison Operators

Comparison operators are used to comparing the two operands and return the result based upon the comparison’s output. It will check if the row returns True or False for each row, and then it will return all the rows which match the criteria. The different operators that come under this category are as follows: – 

Check out upGrad’s Java Bootcamp.

OperatorOperationDescription
=Equal toIt checks if the values of both the operands are equal or not. It will return the TRUE rows.
<> or !=Not equal toIt checks if the values of both the operands are equal or not. If they are not equal, then it will return TRUE.
>Greater thanIt checks if the value of the left operand is greater than the right operand or not.
<Less thanIt checks if the value of the left operand is less than the right operand or not.
!>Not greater thanIt checks if the left operand’s value is not greater than or equal to the right operand.
!<Not less thanIt checks if the left operand’s value is not less than or equal to the right operand or not.
>=Greater than or equal toIt checks if the left operand’s value is greater than or equal to the right operand or not.
<=Less than or equal toIt checks if the left operand’s value is less than or equal to the right operand or not.

Explore our Popular Software Engineering Courses

3. Logical Operators

Logical operators are the boolean operators that take two expressions as operands and provide output as True or False. The expressions give output as True or False, and the logical operators are used to combine these expressions.

These operators come in handy when we are dealing with complex SQL queries. These operators perform similarly to what we know as logic gates. The different operators that come under this category are as follows: – 

OperatorDescription
ALLReturns the rows for which all of the subqueries meet the condition specified
ANDReturns the rows for which all the conditions separated by AND return True
ANYReturns the rows for which any of the subqueries meet the condition
SOMEReturns the rows for which any of the subqueries meet the condition
LIKEReturns the rows for which the operand matches a pattern
INReturns the rows for which if the operand is equal to one of the lists of expressions
NOTReturns the rows for which the condition(s) is NOT TRUE
ORReturns the rows for which any of the conditions separated by OR is TRUE
BETWEENReturns the rows for which the operand is within the range of comparisons
EXISTSReturns the rows for which the subquery returns one or more records
NULLReturns the rows where the operands are NULL.

4. Compound Operators

Compound operators are used to executing some operations and then set the original value to the query’s value. These operators are not available in some databases like SQLite. You can use them in T-SQL (Transact-SQL), which is an extension of SQL. The different operators that come under this category are as follows: –

OperatorDescription
+=Adds the value to the original value and sets the original value to the result.
-=Subtracts the value from the original value and sets the original value to the result.
*=Multiplies the original value by some value and sets the original value to the result.
/=Divides the original value by some value and sets the original value to the result.
%=Performs modulus and sets the original value to the result.
&=Performs Bitwise AND operation and sets the original value to the result.
^-=Performs Bitwise EX-OR operation and sets the original value to the result.
|*=Performs Bitwise OR operation and sets the original value to the result.

5. Bitwise Operators

Bitwise operators are used to performing bitwise manipulations between the two expressions containing integers. These operators first convert the integers into bits and then operate on each bit to obtain the result.

The result is then again converted into an integer format to provide the outcome. These operators are seldomly used in SQL queries. The different operators that come under this category are as follows: – 

OperatorDescription
&Used to perform bitwise AND operation
|Used to perform bitwise OR operation
^Used to perform bitwise EX-OR operation

Also Read: Interesting SQL Projects on GitHub

In-Demand Software Development Skills

6. Set Operators

Set operators are used when we want to merge multiple queries using different tables. You can refer to the Venn diagrams to better understand how these set operations exactly work. The different operators that come under this category are as follows: –

OperatorDescription
UNIONThis operator will perform the union of both expressions.
UNION ALLSimilar in function to the union. The difference is this operator gives the result without removing any duplications.
INTERSECTThis operator will display the rows which are present in both expressions.
MINUSThis operator will display the rows present in the first expression but absent in the second expression.

Examples

For illustration purposes, we will refer to one table named ‘cities’ and demonstrate how the operators work using this table. The below image that consists of a set of records will help you understand how the table looks and its different columns. 

7. Arithmetic Operations

Query: – SELECT latd + 2, latd – 2, latd * 2, latd / 2, latd % 2 FROM cities;

In this query, we have used arithmetic operators to showcase how they work on the columns. The arithmetic operators deal with the numeric type of data and provide output accordingly.

8. Comparison Operations

Query: – SELECT * FROM cities WHERE latm <= 20;

This query will show all the rows where late is less than or equal to 20. Similarly, according to your use case, we can use other logical operations like <, >, etc.according to your use-case.

9. Logical Operations

Query: – SELECT * FROM cities WHERE lats BETWEEN 20 AND 50;

This query will display all the rows where the ‘lats’ column’s value lies in the range of 20 to 50, including the end values.

10. Bitwise Operators

Query: – SELECT CAST(latd as bit) | ’11’ FROM Cities;

The above query will perform bitwise OR operation on the operands. First, we need to convert the integer data type to bit values. To achieve this, we make use of the cast function. This function can help you to convert a value into another data type.

Set Operators: 

Query: – 

SELECT and FROM Cities UNION SELECT name FROM demo;

This query will list all the land’s column’s values and ‘name’ column from ‘cities’ and ‘demo’ tables. 

Read: SQL vs PL/SQL

Ads of upGrad blog

Learn Software Courses online from the World’s top Universities. Earn Executive PG Programs, Advanced Certificate Programs, or Masters Programs to fast-track your career.

Explore Our Software Development Free Courses

Conclusion

This article shed some light on what operators are in SQL and the different types of SQL operators. The list of operators mentioned here is not exhaustive. It may also happen that your database may not support some of the operators listed. For example, the operators shown in compound operators are supported in Transact-SQL, which is nothing but an SQL extension.

There can always be some alternate workarounds for performing such operations. Now with a huge amount of data being generated, SQL may not be an efficient solution for data of humungous sizes. Still, it acts as a great tool for storing different applications’ metadata and serving as a back-end database to these applications. 

If you’re interested to learn more about full-stack development, check out upGrad & IIIT-B’s Executive PG Program in Full-stack Software Development which is designed for working professionals and offers 500+ hours of rigorous training, 9+ projects, and assignments, IIIT-B Alumni status, practical hands-on capstone projects & job assistance with top firms.

Profile

Rohan Vats

Blog Author
Software Engineering Manager @ upGrad. Passionate about building large scale web apps with delightful experiences. In pursuit of transforming engineers into leaders.

Frequently Asked Questions (FAQs)

1What is SQL?

SQL stands for Structured Query Language. It is a database language that has been developed by IBM in the 70's and is used by most of the database technology providers like Oracle, MySQL, SQL Server and IBM. It is a standard language that is used to manipulate and retrieve data from any database. SQL is one of the most widely used database languages. SQL is the most widely used programming language in the world for getting data from relational databases. It does not care about the underlying data, but the structure of the data stored in a database. SQL is a programming language used to access databases. It can be used to create, read, write, update and delete information in the database.

2What are operators in SQL?

In SQL, operators are used to perform arithmetic or string operation on variables. There are three types of operators in SQL: Arithmetic Operators, String Operators and Logical Operators. Operators are used in SQL to perform specific mathematical, logical or comparison operations on the records in a database. The comparison operators in SQL are equal to, less than, greater than, less than or equal to, greater than or equal to, not equal to and NULL. The mathematical operators are addition, subtraction, multiplication and division. The logical operators are AND and OR.

3What are some of the most common commands in SQL?

Some common SQL commands are listed below. SELECT returns data from one or more tables. When used in combination with WHERE and ORDER BY, it retrieves specific data from a database. UPDATE allows you to modify records in a database. You can use it to change existing data or add new records. DELETE is used to remove records from a database. Many languages, such as PHP, include SQL database functions which allow you to write SQL statements and access a database without knowing the details of SQL.

Explore Free Courses

Suggested Blogs

8 Exciting Full Stack Coding Project Ideas &amp; Topics For Beginners
3696
A Full stack developer is an engineer who can design and develop an end-to-end application independently by handling all the work of coding, databases
Read More

by upGrad

19 Feb 2024

17 Interesting HTML Project Ideas &#038; Topics For Beginners [2024]
413910
Summary In this article, you will learn 10 Interesting HTML Project Topics. Take a glimpse below. A tribute page A survey form Technical documentati
Read More

by Rohan Vats

19 Feb 2024

How to Open json File in Excel
96515
What is JSON? JSON (JavaScript Object Notation) is a file format that is used for storing and exchanging data in the network. It is used to send data
Read More

by Rohan Vats

19 Feb 2024

JSP vs Servlet: Difference Between JSP &#038; Servlet [2024]
52491
Websites are collections of static files, for example, images, graphics, and HTML files. These websites are referred to as web applications if they pr
Read More

by Rohan Vats

19 Feb 2024

Java Developer Salary in India in 2024 [For Freshers &#038; Experienced]
900411
Wondering what is the range of Java developer salary in India? From choosing your first programming language to writing apps for Android and several
Read More

by Rohan Vats

19 Feb 2024

Polymorphism In OOPS: What is Polymorphism [Detailed Explanation]
124014
Polymorphism in OOPs is inseparable and an essential concept of every object-oriented programming language. An object or reference basically can take
Read More

by Rohan Vats

19 Feb 2024

Literals In Java: Types of Literals in Java [With Examples]
8049
Summary: In this article, you will learn about Literals in Java. Literals in Java Integral Literals Floating-Point Literals Char Literals String Lit
Read More

by Rohan Vats

19 Feb 2024

Top 30 Exception Handling Interview Questions and Answers [For Freshers &amp; Experienced]
35750
Exception handling is a concept that is implemented in algorithms to handle possible runtime errors, which may disrupt the normal flow of a program. S
Read More

by Rohan Vats

19 Feb 2024

Top 40 MySQL Interview Questions &#038; Answers For Beginners &#038; Experienced [2024]
129247
Have a Data engineering or data science interview coming up? Need to practice some of the most asked MySQL interview questions? The article compiles t
Read More

by Rohan Vats

19 Feb 2024

Schedule 1:1 free counsellingTalk to Career Expert
icon
footer sticky close icon