Explain different comparison operators used in SQL queries

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

For example,

select ename “employee” from emp where sal=2000;

For example,

select ename from emp where sal^=4000;

For example,

select ename, job from employee wher salary>5000;

For example,

select * from emp where salary<5000;

For example,

select * from emp where price>=500;

For example,

select ename from emp where sal<=4000;

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);

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;

Published on 03-Jul-2021 08:55:03