SQL LOGICAL OPERATORS

The Logical Operator is nothing but which returns the result in one form, i.e., either it will display the query is true, or the query is false. The results displayed to combine or merge more than one true or false data.

The Logical Operators in SQL are as follows:

SQL AND OPERATOR
SQL OR OPERATOR
SQL NOT OPERATOR
SQL BETWEEN OPERATOR
SQL IN OPERATOR
SQL LIKE OPERATOR

Let's understand each and every operator one by one with the help of examples. All the queries in the examples will be written using the MySQL database.

Consider we have an employees table with the following data:

E_ID	Name	Salary	City	Designation	Date_of_Joining	Age
1	Sakshi Kumari	50000	Mumbai	Project Manager	2021-06-20	24
2	Tejaswini Naik	75000	Delhi	System Engineer	2019-12-24	23
3	Anuja Sharma	40000	Jaipur	Manager	2021-08-15	26
4	Anushka Tripathi	90000	Mumbai	Software Tester	2021-06-13	24
5	Rucha Jagtap	45000	Bangalore	Project Manager	2020-08-09	23
6	Rutuja Deshmukh	60000	Bangalore	Manager	2019-07-17	26
7	Swara Baviskar	55000	Jaipur	System Engineer	2021-10-10	24
8	Sana Sheik	45000	Pune	Software Engineer	2020-09-10	26
9	Swati Kumari	50000	Pune	Software Tester	2021-01-01	25
10	Mayuri Patel	60000	Mumbai	Project Manager	2020-10-02	24
11	Simran Khanna	45500	Kolhapur	HR	2019-01-02	26
12	Shivani Wagh	50500	Delhi	Software Developer	2016-09-10	25
13	Kiran Maheshwari	50000	Nashik	HR	2013-12-12	23
14	Tejal Jain	40000	Delhi	Project Manager	2017-11-10	25
15	Mohini Shah	38000	Pune	Software Developer	2019-03-05	20

1. SQL AND Operator

The SQL AND operator is used with the where clause in the SQL Query. AND operator in SQL returns only those records which satisfy both the conditions in the SQL query.

Let's understand the below example, which explains how to execute AND operator in an SQL query.

Example:

Write a query to retrieve only those records of employees from the employees table where the designation is 'Project Manager' and the City to which the employee belongs to is Mumbai.

Query:

mysql> SELECT * FROM employees WHERE City = "Mumbai" AND Designation = "Project Manager";

Here we have written a SELECT query with a WHERE clause on the City column and Designation column with 'AND' operator in between both the conditions. Any record in the employees table that meets both conditions, i.e., the city to which the employee belongs is Mumbai, and their designation is Project Manager, will only be considered in output.

You will get the following output:

E_ID	Name	Salary	City	Designation	Date_of_Joining	Age
1	Sakshi Kumari	50000	Mumbai	Project Manager	2021-06-20	24
10	Mayuri Patel	60000	Mumbai	Project Manager	2020-10-02	24

There are only two records in the employees table whose city name is equal to 'Mumbai' and designation name is equal to 'Project Manager'.

2. SQL BETWEEN Operator

This operator displays the records which fall between the given ranges in the SQL query. The results of the BETWEEN operator include begin and end values of the given range.

Let's understand the below example, which explains how to execute BETWEEN operator in an SQL query.

Example:

Write a query to retrieve only those records of an employee from the employees table where employee salary lies between 50000 to 90000.

Query:

mysql> SELECT * FROM employees WHERE Salary BETWEEN 50000 AND 90000;

Here we have written a SELECT query with a WHERE clause on the Salary column with the 'BETWEEN' operator. BETWEEN operator is followed by beginning and end values 50000 and 90000 respectively with 'AND' operator in between. Any record in the employees table that meets the condition, i.e., the employee's salary is between 50000 and 90000, will only be considered in output.

You will get the following output:

E_ID	Name	Salary	City	Designation	Date_of_Joining	Age
1	Sakshi Kumari	50000	Mumbai	Project Manager	2021-06-20	24
2	Tejaswini Naik	75000	Delhi	System Engineer	2019-12-24	23
4	Anushka Tripathi	90000	Mumbai	Software Tester	2021-06-13	24
6	Rutuja Deshmukh	60000	Bangalore	Manager	2019-07-17	26
7	Swara Baviskar	55000	Jaipur	System Engineer	2021-10-10	24
9	Swati Kumari	50000	Pune	Software Tester	2021-01-01	25
10	Mayuri Patel	60000	Mumbai	Project Manager	2020-10-02	24
12	Shivani Wagh	50500	Delhi	Software Developer	2016-09-10	25
13	Kiran Maheshwari	50000	Nashik	HR	2013-12-12	23

There are nine records in the employees table whose salary falls between 50000 to 90000.

3. SQL OR Operator

The SQL OR operator is used with the where clause in an SQL Query. AND operator in SQL returns only those records that satisfy any of the conditions in the SQL query.

Let's understand the below example, which explains how to execute OR operator an SQL query.

Example:

Write a query to retrieve only those records of employees from the employees table where the employee's designation is 'System Engineer' or the city to which the employee belongs is Mumbai.

Query:

mysql> SELECT * FROM employees WHERE Designation = "System Engineer" OR City = "Mumbai";

Here we have written a SELECT query with a WHERE clause on the City column and Designation column with the 'OR' operator in between both the conditions. Any record in the employees table that meets any of the conditions, i.e., the city to which the employee belongs is Mumbai, or their designation is System Engineer, will only be considered in output.

You will get the following output:

E_ID	Name	Salary	City	Designation	Date_of_Joining	Age
1	Sakshi Kumari	50000	Mumbai	Project Manager	2021-06-20	24
2	Tejaswini Naik	75000	Delhi	System Engineer	2019-12-24	23
4	Anushka Tripathi	90000	Mumbai	Software Tester	2021-06-13	24
7	Swara Baviskar	55000	Jaipur	System Engineer	2021-10-10	24
10	Mayuri Patel	60000	Mumbai	Project Manager	2020-10-02	24

There are only five records in the employees table whose city name is equal to 'Mumbai' or the employee's designation is equal to 'System Engineer'.

4. SQL IN Operator

When we want to check for one or more than one value in a single SQL query, we use IN operator with the WHERE clause in a SELECT query.

Let's understand the below example, which explains how to execute IN operator in an SQL query.

Example:

Write a query to retrieve only those records of employees from the employees table where the city to which the employee belongs to is either Mumbai, Bangalore, or Pune.

Query:

mysql> SELECT * FROM employees WHERE City IN ("Mumbai", "Bangalore", "Pune");

Here we have written a SELECT query with a WHERE clause on the City column followed by IN operator. Since we wanted only those records that belongs to Mumbai, Bangalore, or Pune, we have passed Mumbai, Bangalore, and Pune as parameters to the IN operator. So, if the City value of any record matches with the places passed to the IN operator will only be considered in output.

You will get the following output:

E_ID	Name	Salary	City	Designation	Date_of_Joining	Age
1	Sakshi Kumari	50000	Mumbai	Project Manager	2021-06-20	24
4	Anushka Tripathi	90000	Mumbai	Software Tester	2021-06-13	24
5	Rucha Jagtap	45000	Bangalore	Project Manager	2020-08-09	23
6	Rutuja Deshmukh	60000	Bangalore	Manager	2019-07-17	26
8	Sana Sheik	45000	Pune	Software Engineer	2020-09-10	26
9	Swati Kumari	50000	Pune	Software Tester	2021-01-01	25
10	Mayuri Patel	60000	Mumbai	Project Manager	2020-10-02	24
15	Mohini Shah	38000	Pune	Software Developer	2019-03-05	20

There are only eight records in the employees table where the city to which the employee belongs is either Mumbai, Bangalore, or Pune.

5. SQL NOT Operator

NOT operator in SQL shows those records from the table where the criteria is not met. NOT operator is used with where clause in a SELECT query.

Let's understand the below example, which explains how to execute NOT operator in SQL query.

Example:

Write a query to retrieve only those records of employees from the employees table where the employee's designation is not Project Manager.

Query:

mysql> SELECT * FROM employees WHERE NOT Designation = "Project Manager";

Here we have written a SELECT query with a WHERE clause on the Designation column followed by NOT operator. Since we wanted only those records whose designation is other than a project manager, we have given the designation value as Project Manager to the NOT operator. So, if the designation value of any record does not match with the value given to the NOT operator will only be considered in output.

You will get the following output:

E_ID	Name	Salary	City	Designation	Date_of_Joining	Age
2	Tejaswini Naik	75000	Delhi	System Engineer	2019-12-24	23
3	Anuja Sharma	40000	Jaipur	Manager	2021-08-15	26
4	Anushka Tripathi	90000	Mumbai	Software Tester	2021-06-13	24
6	Rutuja Deshmukh	60000	Bangalore	Manager	2019-07-17	26
7	Swara Baviskar	55000	Jaipur	System Engineer	2021-10-10	24
8	Sana Sheik	45000	Pune	Software Engineer	2020-09-10	26
9	Swati Kumari	50000	Pune	Software Tester	2021-01-01	25
11	Simran Khanna	45500	Kolhapur	HR	2019-01-02	26
12	Shivani Wagh	50500	Delhi	Software Developer	2016-09-10	25
13	Kiran Maheshwari	50000	Nashik	HR	2013-12-12	23
15	Mohini Shah	38000	Pune	Software Developer	2019-03-05	20

There are eleven records in the employees table whose designation is not a project manager.

6. SQL LIKE Operator

LIKE Operator in SQL displays only those data from the table which matches the pattern specified in the query. Percentage (%) and underscore (_) are the two wildcard operators used with LIKE Operator to perform pattern matching tasks.

Let's understand the below example, which explains how to execute the LIKE operator in an SQL query.

Example:

Write a query to retrieve only those records of employees from the employees table whose salary starts with the digit 5.

Query:

mysql> SELECT * FROM employees WHERE Salary LIKE "5%";

Here we have written a SELECT query with a WHERE clause on the Salary column followed by the LIKE operator. Since we wanted only those records whose salary starts with the digit 5, we have given the value to the LIKE operator as '5%'. So, if the salary value of any record starts with the digit 5, followed by any other digit will only be considered in output.

You will get the following output:

E_ID	Name	Salary	City	Designation	Date_of_Joining	Age
1	Sakshi Kumari	50000	Mumbai	Project Manager	2021-06-20	24
7	Swara Baviskar	55000	Jaipur	System Engineer	2021-10-10	24
9	Swati Kumari	50000	Pune	Software Tester	2021-01-01	25
12	Shivani Wagh	50500	Delhi	Software Developer	2016-09-10	25
13	Kiran Maheshwari	50000	Nashik	HR	2013-12-12	23

There are five records in the employees table whose salary starts with the digit 5.

SQL Tutorial

SQL Database

SQL Table

SQL Select

SQL Clause

SQL Order By

SQL Insert

SQL Update

SQL Delete

SQL Join

SQL Keys

Difference

SQL Injection

SQL String Functions

Miscl

SQL MCQ

PL/SQL Tutorial

Sql Interview Question

SQl Quiz