Explain the logical operators in DBMS

Logical operators are used to specify conditions in the structured query language (SQL) statement. They are also used to serve as conjunctions for multiple conditions in a statement.

The different logical operators are shown below −

ALL − It is used to compare a value with every value in a list or returned by a query. Must be preceded by =, !=, >, < ,<=, or >= evaluates.

For example,

select * from emp where salary>= ALL(1500,4000);

AND − Returns TRUE if both component conditions are TRUE. Returns FALSE if either is FALSE; otherwise returns UNKNOWN.

For example,

select * from emp where job=’manager’ AND deptno=20;

OR − Return TRUE if either component condition is TRUE. Return FALSE if both are FALSE. Otherwise, return UNKNOWN.

For example,

select * from emp where job=’manager’ OR deptno=20;

IN − It is equivalent to any test. Equivalent to = ANY, The In operator is used to compare a value to a list of literal values that have been specified.

For example,

select * from emp where ename IN (‘bhanu’,’ward’);

NOT − Returns TRUE if the condition is FALSE. Returns FALSE, if it is TRUE. If it is UNKNOWN, it remains UNKNOWN.

For example,

select * from emp where NOT (job is NULL)
select * from emp where NOT(salary between 2000 AND 5000);

BETWEEN − It is used to define range limits.

For example,

If we want to find all employees whose age is in between 40 and 50 the query is as follows −

Select * from employee E where E.age between 40 AND 50;

LIKE − It is used to compare values to a list of literal values that are specified. “%” character is used to match any substring and “_” character is used to match any character. It expresses a pattern by using the ‘like’ comparison operator.

For example,

To display all names whose second letter is ‘b’, use the below mentioned command −

select * from emp where ename LIKE ‘_b%’;

To display a person details whose first letter is ‘A’ and third letter is ‘d’, use the command given below −

Select * from emp where ename LIKE ‘A_d_’;