Logical Operators | Database

Introduction

Logical operator allows to test for the truth of a condition. Similar to a comparison operator, a logical operator returns a value of true, false, or unknown. Logical operators are:

AND Operator

This operator allows you to construct multiple conditions in the WHERE clause of an SQL statement. AND operator returns true if both expressions evaluate to true. Following is the syntax

expression1 AND expression2

OR Operator

This operator combines multiple conditions in an SQL statement’s WHERE clause. OR operator returns true if a least one expression evaluates to true.

expression1 OR expression2

NOT Operator

NOT operator takes a single boolean as an argument and changes its value from false to true or from true to false. Following is the syntax

NOT expression

Example

Consider below employee info table.

Below example shows the sample usage of AND, NOT and OR operator.

SELECT * FROM employleinfo WHERE age > 19 AND age < 50

/* Output of above query */
id  firstName lastName  age city  
1 Rahul Bhagwat 20  Noida 
5 Erica Edwards 40  Chennai 
7 Shekar  Chandra 43  Delhi 
9 Garima  Singh 30  Lucknow 


SELECT * FROM employleinfo WHERE age > 19 OR age < 50

/* Output of above query */
id  firstName lastName  age city  
1 Rahul Bhagwat 20  Noida 
2 Stephen Fleming 17  Delhi 
3 Sebastian Smith 19  Patna 
4 New Name  Sharma  50  Mumbai  
5 Erica Edwards 40  Chennai 
6 Priya Gowda 50  Pune  
7 Shekar  Chandra 43  Delhi 
9 Garima  Singh 30  Lucknow 
8 Rahul Singh 19  Patna 


SELECT * FROM employleinfo WHERE age > 19 AND NOT age < 50

/* Output of above query */
id  firstName lastName  age city  
4 New Name  Sharma  50  Mumbai  
6 Priya Gowda 50  Pune  

Special Operators

IS NULL

IS NULL operator compares a value with a null value and returns true if the compared value is null; otherwise, it returns false. For example, the following statement finds all employees whose age is not known.

SELECT firstName, lastName
FROM employleinfo
WHERE age IS NULL;

BETWEEN Operator

BETWEEN operator searches for values that are within a set of values, given the minimum value and maximum value. For example, the following statement finds all employees whose age s are between range.

SELECT firstName, lastName
FROM employleinfo
WHERE age BETWEEN 19 AND 40

IN Operator

IN operator compares a value to a list of specified values. The IN operator returns true if the compared value matches at least one value in the list; otherwise, it returns false. The following statement finds all employees based on age.

SELECT firstName, lastName, age
FROM employleinfo
WHERE age IN (19, 40)

/* Output of query */
firstName lastName  age
Sebastian Smith 19
Erica Edwards 40
Rahul Singh 19

LIKE Operator

LIKE operator compares a value to similar values using a wildcard operator. SQL provides two wildcards used in conjunction with the LIKE operator:

• Percent sign ( %) represents zero, one, or multiple characters.
• Underscore sign ( _) represents a single character.

SELECT firstName, lastName, age
FROM employleinfo
WHERE firstName LIKE '%ri%' OR lastName LIKE 'Smit_'

/* Output of query */
firstName lastName  age
Sebastian Smith     19
Erica     Edwards   40
Priya     Gowda     50
Garima    Singh     30

ALL Operator

This operator compares a value to all values in another value set. The ALL operator must be preceded by a comparison operator and followed by a subquery. The following example finds all employees whose age are greater than all age of employees in a given city.

SELECT  firstName, lastName, age, city
FROM employleinfo
WHERE age >= ALL (
    SELECT age
    FROM employleinfo
    WHERE city ='Patna')

/* Query output */
firstName lastName  age city
Rahul     Bhagwat   20  Noida
Sebastian Smith     19  Patna
New Name  Sharma    50  Mumbai
Erica     Edwards   40  Chennai
Priya     Gowda     50  Pune
Shekar    Chandra   43  Delhi
Garima    Singh     30  Lucknow
Rahul     Singh     19  Patna

Reference

SQL Logical Operators