Explain different comparison operators used in SQL queries

DBMSDatabaseBig Data Analytics

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;
raja
Published on 03-Jul-2021 08:55:03
Advertisements