SQL LOGICAL Operator

In this tutorial, we will understand the operator who falls under the logical operator in SQL with the help of examples.

The SQL Logical Operator displays the query result in one form. The query will display the data, whether it is true or false. The Logical operator is used to return true data or false data to merge one or more than one true or false data.

The operator falls under the logical operator category is as follows:

1. SQL OR Operator
2. SQL AND Operator
3. SQL NOT Operator      
4. SQL BETWEEN Operator
5. SQL IN Operator
6. SQL LIKE Operator

Let's learn about each operator one by one with the help of examples

Consider the already existing table, which has the following data:

Table Number 1: - Diploma_Student

Student_Id	Student_Name	First_Sem	Second_Sem	Third_Sem	Fourth_Sem	Fifth_Sem	Sixth_Sem	Total	Department_Id
202111	Vaishnavi Patil	94	91	88	85	95	92	91	6
202112	Vaibhav Lokhande	85	90	92	80	85	82	86	2
202113	Yash Dhull	90	88	94	87	85	90	89	3
202114	Sonali Patole	95	90	92	88	92	90	91	4
202115	Axar Patel	85	80	82	86	92	84	85	1
202116	Meena Mishra	78	75	80	74	85	77	78	3
202117	Mahesh Kumbhar	75	80	75	78	80	76	77	5
202118	Sakshi Patil	80	78	74	78	80	77	78	2
202119	Sopan Bhore	70	68	75	75	80	80	75	2
202220	Prajwal Lokhande	80	85	85	75	78	80	81	4
202221	Anuja Wanare	85	88	86	82	84	85	85	7
202222	Venkatesh Iyer	90	89	87	90	92	91	90	3
202223	Anushka Sen	70	75	71	74	80	78	75	1
202224	Aakash Jain	80	75	72	74	85	80	78	7
202225	Akshay Agarwal	85	80	78	88	90	82	84	7
202226	Shwetali Bhagwat	90	80	85	88	90	80	86	6
202227	Mayuri Wagh	80	80	85	80	82	85	82	4
202228	Utkarsh Rokade	85	80	80	90	84	84	84	5
202229	Manthan Koli	85	75	84	78	82	80	81	2
202230	Mayur Jain	80	88	87	90	92	90	88	1

.

Table Number 2: Department

Department_Id	Department_Name
1	Computer Engineering
2	Information Technology
3	Mechanical Engineering
4	Automobile Engineering
5	Civil Engineering
6	Electrical Engineering
7	Electronics and Telecommunication Engineering
8	Chemical Engineering

1. SQL OR Operator:

The SQL OR Operator displays those values that meet any conditions in the given SQL query. The OR operator is used with the SELECT statement, the UPDATE statement, and the DELETE statement.

Let’s understand how to use the OR operator in the SQL query with the help of an example.

Example: Execute a query to display the student’s information from the Diploma_Student table where the department id is 2, OR the fifth-semester percentage is greater than 80.

SELECT * FROM Diploma_Student WHERE Department_Id = 2 OR Fifth_Sem > 80;

Here, we have executed the SELECT query with the WHERE clause on the Department_Id field and the Fifth_Sem Field with the OR operator in between both the conditions. Any data in the Diploma_Student table that satisfies any given expression in the query will only be considered in the output.

The output of the above query is:

Student_Id	Student_Name	First_Sem	Second_Sem	Third_Sem	Fourth_Sem	Fifth_Sem	Sixth_Sem	Total	Department_Id
202111	Vaishnavi Patil	94	91	88	85	95	92	91	6
202112	Vaibhav Lokhande	85	90	92	80	85	82	86	2
202113	Yash Dhull	90	88	94	87	85	90	89	3
202114	Sonali Patole	95	90	92	88	92	90	91	4
202115	Axar Patel	85	80	82	86	92	84	85	1
202116	Meena Mishra	78	75	80	74	85	77	78	3
202118	Sakshi Patil	80	78	74	78	80	77	78	2
202119	Sopan Bhore	70	68	75	75	80	80	75	2
202221	Anuja Wanare	85	88	86	82	84	85	85	7
202222	Venkatesh Iyer	90	89	87	90	92	91	90	3
202224	Aakash Jain	80	75	72	74	85	80	78	7
202225	Akshay Agarwal	85	80	78	88	90	82	84	7
202226	Shwetali Bhagwat	90	80	85	88	90	80	86	6
202227	Mayuri Wagh	80	80	85	80	82	85	82	4
202228	Utkarsh Rokade	85	80	80	90	84	84	84	5
202229	Manthan Koli	85	75	84	78	82	80	81	2
202230	Mayur Jain	80	88	87	90	92	90	88	1

2. SQL AND Operator

The SQL AND Operator are used to display those values which meet both the conditions in the given SQL query. The AND operator is used with the SELECT statement, the UPDATE statement, and the DELETE statement.

Let’s understand how to use the AND operator in the SQL query with the help of an example.

Example: Execute a query to display the student’s information from the Diploma_Student table where the First_Sem percentage is greater than 75 and the Second_Sem percentage is less than 90.

SELECT * FROM Diploma_Student WHERE First_Sem > 75 AND Second_Sem < 90;

Here, we have executed the SELECT query with the WHERE clause on the First_Sem field and the Second_Sem Field with the AND operator in between both the conditions. Any data in the Diploma_Student table that satisfies the query's given expression will only be considered in the output.

The output of the above query is as follows:

Student_Id	Student_Name	First_Sem	Second_Sem	Third_Sem	Fourth_Sem	Fifth_Sem	Sixth_Sem	Total	Department_Id
202113	Yash Dhull	90	88	94	87	85	90	89	3
202115	Axar Patel	85	80	82	86	92	84	85	1
202116	Meena Mishra	78	75	80	74	85	77	78	3
202118	Sakshi Patil	80	78	74	78	80	77	78	2
202220	Prajwal Lokhande	80	85	85	75	78	80	81	4
202221	Anuja Wanare	85	88	86	82	84	85	85	7
202222	Venkatesh Iyer	90	89	87	90	92	91	90	3
202224	Aakash Jain	80	75	72	74	85	80	78	7
202225	Akshay Agarwal	85	80	78	88	90	82	84	7
202226	Shwetali Bhagwat	90	80	85	88	90	80	86	6
202227	Mayuri Wagh	80	80	85	80	82	85	82	4
202228	Utkarsh Rokade	85	80	80	90	84	84	84	5
202229	Manthan Koli	85	75	84	78	82	80	81	2
202230	Mayur Jain	80	88	87	90	92	90	88	1

3 SQL NOT Operator

The SQL NOT Operator displays the reverse values of the conditions given in the SQL query. The NOT operator is used with the SELECT query, the UPDATE query, and the DELETE query.

Let’s understand how to use the NOT operator in the SQL query with the help of an example.

Example: Execute a query to display the student’s information from the Diploma_Student table where the total percentage is not between 85 and 92.

SELECT * FROM Diploma_Student WHERE Total NOT BETWEEN 85 AND 92;

Since, we want to display only those student's information whose total percentage is not between the 85 and 92 in the above query

The output of the above query is as follows:

Student_Id	Student_Name	First_Sem	Second_Sem	Third_Sem	Fourth_Sem	Fifth_Sem	Sixth_Sem	Total	Department_Id
202116	Meena Mishra	78	75	80	74	85	77	78	3
202117	Mahesh Kumbhar	75	80	75	78	80	76	77	5
202118	Sakshi Patil	80	78	74	78	80	77	78	2
202119	Sopan Bhore	70	68	75	75	80	80	75	2
202220	Prajwal Lokhande	80	85	85	75	78	80	81	4
202223	Anushka Sen	70	75	71	74	80	78	75	1
202224	Aakash Jain	80	75	72	74	85	80	78	7
202225	Akshay Agarwal	85	80	78	88	90	82	84	7
202227	Mayuri Wagh	80	80	85	80	82	85	82	4
202228	Utkarsh Rokade	85	80	80	90	84	84	84	5
202229	Manthan Koli	85	75	84	78	82	80	81	2

4. SQL BETWEEN Operator:

The SQL BETWEEN Operator displays the record between the starting and ending values.

Let’s understand how to use the BETWEEN operator in the SQL query with the help of an example.

Example: Execute a query to display the student’s information from the Diploma_Student table where department id is between 2 and 5.

SELECT * FROM Diploma_Student WHERE Department_Id BETWEEN 2 AND 5;

We have displayed the student's records whose department id is between 2 and 5 in the above query.

The output of the above query is as follows:

Student_Id	Student_Name	First_Sem	Second_Sem	Third_Sem	Fourth_Sem	Fifth_Sem	Sixth_Sem	Total	Department_Id
202112	Vaibhav Lokhande	85	90	92	80	85	82	86	2
202113	Yash Dhull	90	88	94	87	85	90	89	3
202114	Sonali Patole	95	90	92	88	92	90	91	4
202116	Meena Mishra	78	75	80	74	85	77	78	3
202117	Mahesh Kumbhar	75	80	75	78	80	76	77	5
202118	Sakshi Patil	80	78	74	78	80	77	78	2
202119	Sopan Bhore	70	68	75	75	80	80	75	2
202220	Prajwal Lokhande	80	85	85	75	78	80	81	4
202222	Venkatesh Iyer	90	89	87	90	92	91	90	3
202227	Mayuri Wagh	80	80	85	80	82	85	82	4
202228	Utkarsh Rokade	85	80	80	90	84	84	84	5
202229	Manthan Koli	85	75	84	78	82	80	81	2

5. SQL IN Operator

The SQL IN Operator is used to list out one or more than one values in the SQL query

Let’s understand how to use the IN operator in the SQL query with the help of an example.

Example: Execute a query to display the student’s information from the Diploma_Student table where department name is ‘Mechanical Engineering’, ‘Automobile Engineering’, and ‘Civil Engineering’.

SELECT * FROM Diploma_Student WHERE Department_Id IN (SELECT Department_Id FROM Department WHERE Department_Name IN ('Civil Engineering', 'Mechanical Engineering', 'Automobile Engineering'));

We have displayed the student's information where department name is 'Mechanical Engineering', 'Automobile Engineering', and 'Civil Engineering'.

The output of the above query is as follows:

Student_Id	Student_Name	First_Sem	Second_Sem	Third_Sem	Fourth_Sem	Fifth_Sem	Sixth_Sem	Total	Department_Id
202113	Yash Dhull	90	88	94	87	85	90	89	3
202116	Meena Mishra	78	75	80	74	85	77	78	3
202222	Venkatesh Iyer	90	89	87	90	92	91	90	3
202114	Sonali Patole	95	90	92	88	92	90	91	4
202220	Prajwal Lokhande	80	85	85	75	78	80	81	4
202227	Mayuri Wagh	80	80	85	80	82	85	82	4
202117	Mahesh Kumbhar	75	80	75	78	80	76	77	5
202228	Utkarsh Rokade	85	80	80	90	84	84	84	5

6. SQL LIKE Operator

The SQL LIKE Operator displays only those records from the table that match the specific pattern mentioned in the query. The two pattern matching wildcard characters used with the LIKE operator are Percentage (%) and Underscore (_).

Let’s understand how to use the LIKE operator in the SQL query with the help of an example.

Example: Execute a query to display the student’s information from the Diploma_Student table where the Student name starts with the letter 'S'.

SELECT * FROM Diploma_Student WHERE Student_Name LIKE 'S%';

We have fetched those student’s information whose name starts with the letter ‘S’.

The output of the above query is as follows:

Student_Id	Student_Name	First_Sem	Second_Sem	Third_Sem	Fourth_Sem	Fifth_Sem	Sixth_Sem	Total	Department_Id
202114	Sonali Patole	95	90	92	88	92	90	91	4
202118	Sakshi Patil	80	78	74	78	80	77	78	2
202119	Sopan Bhore	70	68	75	75	80	80	75	2
202226	Shwetali Bhagwat	90	80	85	88	90	80	86	6