DBMS: SQL Operator

In this article, you will learn the various operators that can be used in SQL queries. Operators in SQL can be categorised as follows:

1. Arithmetic operator
2. Comparison operator
3. Logical operator

1. SQL Arithmetic Operators

Operator	Description
+	Addition
_	Subtraction
*	Multiplication
/	Division
%	Modulus

Arithmetic Operator Examples:

This can be simply used to add two numbers:
SELECT 50 + 30;
Result: 80

Similarly subtraction operator can be used:
SELECT 30 - 20 -10;
Result: 0

Multiplication operator:
SELECT 10* 10 * 10;
Result: 1000

Division Operator: returns the quotient
SELECT 20 / 5;
Result: 4

Modulo operator: returns the remainder
SELECT 21 % 5;
Result: 1

2. SQL Comparison Operators

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

Comparison Operator Examples:

This will fetch all the records of employees from EMPLOYEE table where employee age is equal to 18:

SELECT * FROM EMPLOYEE
WHERE age = 18;

This will fetch the records of those employees from EMPLOYEE table who have salary greater than 10000:

SELECT * FROM EMPLOYEE
WHERE salary > 10000;

This will fetch the records of those employees from EMPLOYEE table who have salary less than 30000:

SELECT * FROM EMPLOYEE
WHERE salary < 30000;

This will fetch the records of employee who have salary equal to or greater than 15000:

SELECT * FROM EMPLOYEE
WHERE salary >= 15000;

This will fetch the records of employee who have salary equal to or greater than 25000:

SELECT * FROM EMPLOYEE
WHERE salary <= 25000;

This will fetch the records from EMPLOYEE table where the employee age is not equal to 18, which means it will get all the records except the records where employee age is 18:

SELECT * FROM EMPLOYEE
WHERE age <> 18;

3. SQL Logical Operators

Operator	Description
ALL	Returns true if all the subquery values fulfil the condition. This operator is generally use along with a subquery. Values generated by the subquery is used by the main query in where clause. Example will make it clear, refer example below.
AND	Returns if all the conditions that are separated by AND operator are true.
ANY	Returns true if all the subquery values fulfil the condition.
EXISTS	Returns true if the subquery returns one or more records.
BETWEEN	Used in SQL query where we need to check the condition for values between a certain range, these ranges can be defined in where condition using BETWEEN operator.
IN	Compares the value to a specified list of values.
LIKE	It compares the value to similar values using wildcard operator.
OR	OR operator is used to combine multiple conditions in SQL query.
NOT	Used along with other logical operators and reverses their meaning for example, NOT IN will compares the values except the specified list of values.
SOME	Returns true if any of the subquery value fulfil the condition.

Logical Operator Examples:

The subquery will fetch the student ids for all the students from SCHOOL table where age is greater than 18, these ids will be used by main query that will fetch the name of these students from STUDENT table:

SELECT name FROM STUDENT
WHERE studentID = ALL (
SELECT studentID FROM SCHOOL WHERE age > 18
);

This will fetch the records of students where city is 'Agra' and country is 'INDIA'. This will only fetch those records where both of these conditions are true, for example it will not fetch the record where country is 'INDIA' but city is other than 'Agra':

SELECT * FROM STUDENT
WHERE City = "Agra" AND Country = "INDIA";

This will fetch all the records where student age lies between 18 and 22:

SELECT * FROM STUDENT
WHERE age BETWEEN 18 AND 22;

The following query demonstrate the use of NOT and LIKE operator. It will fetch the names of the students from STUDENT table where name does not start with letter 'a' or 'A':

SELECT name FROM STUDENT
WHERE name NOT LIKE 'a%';