Open In App

SQL Operators

Improve
Improve
Like Article
Like
Save
Share
Report

Pre-requisites: What is SQL?

Structured Query Language is a computer language that we use to interact with a relational database. In this article we will see all types of SQL operators. 

In simple operator can be defined as an entity used to perform operations in a table.

Operators are the foundation of any programming language. We can define operators as symbols that help us to perform specific mathematical and logical computations on operands. In other words, we can say that an operator operates the operands. SQL operators have three different categories.

Types of SQL Operators

Arithmetic Operators

We can use various arithmetic operators on the data stored in the tables. Arithmetic Operators are:

Operator Description
+

The addition is used to perform an addition operation on the data values.

This operator is used for the subtraction of the data values.

/

This operator works with the ‘ALL’ keyword and it calculates division operations.

*

This operator is used for multiplying data values.

%

Modulus is used to get the remainder when data is divided by another.

Example Query:

SELECT * FROM employee WHERE emp_city NOT LIKE 'A%';

Output:

 

Comparison Operators

Another important operator in SQL is a comparison operator, which is used to compare one expression’s value to other expressions. SQL supports different types of comparison operator, which is described below:

Operator Description
= Equal to.
> Greater than.
< Less than.
>= Greater than equal to.
<= Less than equal to.
<> Not equal to.

Example Query:

SELECT * FROM MATHS WHERE MARKS=50;

Output:

 

Logical Operators

The Logical operators are those that are true or false. They return true or false values to combine one or more true or false values.

Operator Description
AND

Logical AND compares two Booleans as expressions and returns true when both expressions are true.

OR

Logical OR compares two Booleans as expressions and returns true when one of the expressions is true.

NOT

Not takes a single Boolean as an argument and change its value from false to true or from true to false.

Example Query:

SELECT * FROM employee WHERE emp_city = 
'Allahabad' AND emp_country = 'India';

Output:

 

Special Operators

Operators               Description 
ALL 

ALL is used to select all records of a SELECT STATEMENT. It compares a value to every value in a list of results from a query. The ALL must be preceded by the comparison operators and evaluated to TRUE if the query returns no rows.

ANY

ANY compares a value to each value in a list of results from a query and evaluates to true if the result of an inner query contains at least one row.

BETWEEN

The SQL BETWEEN operator tests an expression against a range. The range consists of a beginning, followed by an AND keyword and an end expression.

IN

The IN operator checks a value within a set of values separated by commas and retrieves the rows from the table that match.

EXISTS

The EXISTS checks the existence of a result of a subquery. The EXISTS subquery tests whether a subquery fetches at least one row. When no data is returned then this operator returns ‘FALSE’.  

SOME SOME operator evaluates the condition between the outer and inner tables and evaluates to true if the final result returns any one row. If not, then it evaluates to false.
UNIQUE The UNIQUE operator searches every unique row of a specified table.

Example Query:

SELECT * FROM employee WHERE emp_id BETWEEN 101 AND 104;

Output:

 


Last Updated : 06 Apr, 2023
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads