- Trending Categories
- Data Structure
- Operating System
- MS Excel
- C Programming
- Social Studies
- Fashion Studies
- Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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.
= : equal operator is used to test equality.
select ename “employee” from emp where sal=2000;
!=, ^=, <> : These operators are used for inequality test
select ename from emp where sal^=4000;
> : result the item that is greater than the given condition.
select ename, job from employee wher salary>5000;
<: results the item that is less than the given condition.
select * from emp where salary<5000;
>= : It checks the greater than equal to test.
select * from emp where price>=500;
<= : It checks the less than or equal to test.
select ename from emp where sal<=4000;
IN − “equivalent to any number of “ test. Equivalent “=ANY”
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.
select * from emp where loc=some(‘hyderabad’,’delhi’);
NOT IN − Equivalent to ‘!=ANY’. Evaluate to FALSE if any member of the set is NULL.
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.
select * from emp where sal>=ALL(1500,5000);
Exist − TRUE if a sub-query returns at least one row.
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.
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.
SELECT ename, deptno FROM emp WHERE comm IS NULL;
Kickstart Your Career
Get certified by completing the courseGet Started