Logical Operators in SQL

Logical Operators in SQL

4 mins read486 Views Comment
clickHere
Vikram
Vikram Singh
Assistant Manager - Content
Updated on Nov 22, 2022 19:37 IST

While working with a large dataset you have to perform a different kind of operation to extract or manipulate the the data from the dataset. To perform these operation we nee different types of operators. In this article we will discuss different types of Logical Operator in SQL.

2022_07_SQL-OPERATORS.jpg

In the previous article, we have discussed SQL Comparison and Arithmetic Operator. Now in this article, we will discuss different Logical Operators in SQL.

Table of Content

Logical Operators

Logical Operators

Logical operators are symbols or words that are used to connect two or more expressions. Some of the common logical operators are AND, OR, and NOT. SQL has more than these logical operators.

Here, is the list of Logical Operators in SQL.

Operators Description
AND It will allow selecting only those rows that satisfy both the given conditions
OR It will allow selecting only those rows that satisfy either of the given two conditions
NOT Allows to select the rows that do not satisfy the specified conditions
LIKE Allows to match the similar value or the string
IN It will select a list of values that you want like to include
BETWEEN It will select the rows defined between a certain range. It has to be paired with AND.
ISNULL Allows excluding rows with the missing data from your result

Must Read: SQL SELECT

Must Read: SQL WHERE

Must Read: SQL LIMITS

Now we will use the Employee dataset (that contains Employee ID, Name, Gender, Department, Education, Month of Joining, and CTC) to do some examples to get a clear understanding of different logical operators.

Employee ID Name Gender Department Education Month of Joining CTC(in Lacs)
1001 Ajay M Engineering Doctoral January 25
1002 Babloo M Engineering UG February 23
1003 Chhavi F HR PG March 15
1004 Dheeraj M HR UG January 12
1005 Evina F Marketing UG March 16
1006 Fredy M Sales UG December 10
1007 Garima F Sales PG March 10
1008 Hans M Admin PG November 8
1009 Ivanka F Admin Intermediate April 7
1010 Jai M Peon December 4

Must Read: Introduction to SQL

AND

Example 1: Find the details of all the employees who are working in Sales department and are undergraduates.

Query

 
SELECT *
FROM Employee
WHERE department = ‘Sales’ AND Education = ‘UG’;
Copy code

Output

Employee ID Name Gender Department Education Month of Joining CTC(in Lacs)
1006 Fredy M Sales UG December 10

Must Read: What is the difference between SQL and MySQL?

OR

Example 2: Find the employee detail of all the employees who joined the month of march or CTC is greater than 20 lacs.

Query

 
SELECT *
FROM Employee
WHERE Month of Joining = ‘March’ OR CTC > 20;
Copy code

Output

Employee ID Name Gender Department Education Month of Joining CTC(in Lacs)
1001 Ajay M Engineering Doctoral January 25
1002 Babloo M Engineering UG February 23
1003 Chhavi F HR PG March 15
1005 Evina F Marketing UG March 16
1007 Garima F Sales PG March 10

Must Read: How to Create, Insert, and Delete SQL views?

NOT

Example 3: Find the details of all the employee who are undergraduate but not working in the HR department.

Query

 
SELECT *
FROM Employee
WHERE Education = ‘UG’ AND Department NOT ‘HR’;
Copy code

Output

Employee ID Name Gender Department Education Month of Joining CTC(in Lacs)
1002 Babloo M Engineering UG February 23
1005 Evina F Marketing UG March 16
1006 Fredy M Sales UG December 10

Must Read: Types of Keys in Database

Must Read: Introduction to Normalization

LIKE

LIKE Operator  Description
LIKE ‘n%’ Find the values that start with n
LIKE ‘%g’ Find the values that end with g
LIKE ‘%learn%’ Find the value that has learn in any position
LIKE ‘_n%’ Find the letter that has n at the second position
LIKE ‘n_%’ Find the word that starts with n but has at least two characters in length
LIKE ‘n%g’ Find the word that starts with n and ends with g

Example 4: Find the detail of the employees who are working in the department that ends with ‘ing’.

Query

 
SELECT *
FROM Employee
WHERE Department LIKE%ing’;
Copy code

Output

Employee ID Name Gender Department Education Month of Joining CTC(in Lacs)
1001 Ajay M Engineering Doctoral January 25
1002 Babloo M Engineering UG February 23
1005 Evina F Marketing UG March 16

Must Read: Types of Keys in Database

IN

Example 5: Find the details of the employees who are working in HR and Engineering department.

Query

 
SELECT *
FROM Employee
WHERE Department IN (‘Engineering’, ‘HR’);
Copy code

Output

Employee ID Name Gender Department Education Month of Joining CTC(in Lacs)
1001 Ajay M Engineering Doctoral January 25
1002 Babloo M Engineering UG February 23
1003 Chhavi F HR PG March 15
1004 Dheeraj M HR UG January 12

Must Read: Subqueries in SQL

Must Read: Aggregate and Scalar Function in SQL

BETWEEN

Example 6: Find the employee details whose CTC is in BETWEEN 8 and 15.

Query

 
SELECT *
FROM Employee
WHERE CTC BETWEEN 8 AND 15;
Copy code
Employee ID Name Gender Department Education Month of Joining CTC(in Lacs)
1003 Chhavi F HR PG March 15
1004 Dheeraj M HR UG January 12
1006 Fredy M Sales UG December 10
1007 Garima F Sales PG March 10
1008 Hans M Admin PG November 8

Must Read: Difference between SQL and NoSQL

Must Check: SQL Online Course and Certifications

ISNULL

Example 7: Find the detail of the employees where Education is NULL.

 
SELECT *
FROM Employee
WHERE Education IS NULL;
Copy code

Output

Employee ID Name Gender Department Education Month of Joining CTC(in Lacs)
1010 Jai M Peon December 4

Conclusion

In this article, we have discussed different types of Logical Operators in SQL with the help of examples.

Hope this article will help you in your Data Science and Data Analysis journey.

About the Author
author-image
Vikram Singh
Assistant Manager - Content

Vikram has a Postgraduate degree in Applied Mathematics, with a keen interest in Data Science and Machine Learning. He has experience of 2+ years in content creation in Mathematics, Statistics, Data Science, and Mac... Read Full Bio

Comments