# Explain different comparison operators used in SQL queries

DBMSDatabaseBig Data Analytics

#### DBMS for GATE Exams

178 Lectures 14.5 hours

#### DBMS in Simple Steps

194 Lectures 16 hours

Comparison operators are used in conditions that compare one expression with another. The result of comparison can be TRUE, FALSE or UNKNOWN.

• = : equal operator is used to test equality.

For example,

select ename “employee” from emp where sal=2000;
• !=, ^=, <> : These operators are used for inequality test

For example,

select ename from emp where sal^=4000;
• > : result the item that is greater than the given condition.

For example,

select ename, job from employee wher salary>5000;
• <: results the item that is less than the given condition.

For example,

select * from emp where salary<5000;

• >= : It checks the greater than equal to test.

For example,

select * from emp where price>=500;
• <= : It checks the less than or equal to test.

For example,

select ename from emp where sal<=4000;
• IN − “equivalent to any number of “ test. Equivalent “=ANY”

For example,

select * from emp where ename IN (‘JHON’,’WARD’);
• ANY/SOME − Compare a value to each value in list or returned by a query. Must be preceded by =, !=, >, <, <=, or >=. Evaluate to false if the query returns no rows.

For example,

select * from emp where loc=some(‘hyderabad’,’delhi’);

• NOT IN − Equivalent to ‘!=ANY’. Evaluate to FALSE if any member of the set is NULL.

For example,

select * from dept where loc NOT IN(‘hyderabd’,’KPHB’);
• ALL: Compare a value with every value in a list or returned by a query. Must be preceded by =, !=, >, <, or >=. Evaluate to TRUE if the query returns no rows.

For example,

select * from emp where sal>=ALL(1500,5000);
• Exist − TRUE if a sub-query returns at least one row.

For example,

select * from emp where exists (select ename from emp where manager is NULL);
• [NOT] BETWEEN x AND y − [Not] greater than or equal to x and less than or equal to y.

For example,

SELECT * FROM emp WHERE sal BETWEEN 2000 AND 3000;

• IS [NOT] NULL − Test for nulls. This is the only operator that you should use to test for nulls.

For example,

SELECT ename, deptno FROM emp WHERE comm IS NULL;
Updated on 03-Jul-2021 08:55:42