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;