Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
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.
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;
289 Views
