Logical Operators

The Logical Operators in SQL Server

The Logical operators are used to test for the truth of some condition. Logical operators, like comparison operators, return a Boolean data type with a value of TRUE, FALSE, or UNKNOWN.

In SQL Server there are three Logical Operators such as

  1. AND – TRUE if both Boolean expressions are TRUE.
  2. OR – TRUE if either Boolean expression is TRUE.
  3. NOT – Reverses the value of any other Boolean operator.

These operators are used to compare two conditions at a time to determine whether a row (or rows) can be selected for the output.

When retrieving data using a SELECT statement we can use these logical operators in the WHERE clause to combine more than one condition.

Use below script to create database EmployeeDB, Employee table and populate Employee table with some test data.
--Create database EmployeeDB
Create database EmployeeDB
Go

Use EmployeeDB
Go

-- Create Employee table
CREATE TABLE Employee
(
  ID INT PRIMARY KEY IDENTITY(1,1),
  Name VARCHAR(100),
  EmailID VARCHAR(100),
  Gender VARCHAR(100),
  Department VARCHAR(100),
  Salary INT,
  Age INT,
  CITY VARCHAR(100)
)
GO

--Insert some test data into Employee table
INSERT INTO Employee VALUES('Pranaya','Pranaya@g.com','Male', 'IT', 25000, 30,'Mumbai')
INSERT INTO Employee VALUES('Tarun','Tarun@g.com','Male', 'Payroll', 30000, 27,'Odisha')
INSERT INTO Employee VALUES('Priyanka','Priyanka@g.com','Female', 'IT', 27000, 25,'Bangalore')
INSERT INTO Employee VALUES('Preety','Preety@g.com','Female', 'HR', 35000, 26,'Bangalore')
INSERT INTO Employee VALUES('Ramesh','Ramesh@g.com','Male','IT', 26000, 27,'Mumbai')
INSERT INTO Employee VALUES('Pramod','Pramod@g.com','Male','HR', 29000, 28,'Odisha')
INSERT INTO Employee VALUES('Anurag','Anurag@g.com','Male', 'Payroll', 27000, 26,'Odisha')
INSERT INTO Employee VALUES('Hina','Hina@g.com','Female','HR', 26000, 30,'Mumbai')
INSERT INTO Employee VALUES('Sambit','Sambit@g.com','Male','Payroll', 30000, 25,'Odisha')
INSERT INTO Employee VALUES('Manoj','Manoj@g.com','Male','HR', 30000, 28,'Odisha')
INSERT INTO Employee VALUES('Sara',' Sara@g.com','Female', 'Payroll', 28000, 27,'Mumbai')
INSERT INTO Employee VALUES('Lima','Lima@g.com','Female','HR', 30000, 30,'Bangalore')
INSERT INTO Employee VALUES('Dipak','Dipak@g.com','Male','Payroll', 32000, 25,'Bangalore')
GO
Fetch The Records

Select * From Employee

Logical Operators in SQL Server

Logical AND Operator:

Logical AND operator compares two Booleans as expression and returns TRUE when both of the conditions are TRUE and returns FALSE when either is FALSE; otherwise, returns UNKNOWN (an operator that has one or two NULL expressions returns UNKNOWN).

If we want to select rows that must satisfy all the given conditions, then we can use the logical AND operator.

For Example: To find the employees from the Employee table where the salary between 27000 and 30000, the query would be like:

SELECT *
FROM Employee 
WHERE Salary >= 27000 AND Salary <= 30000;

The above SQL statement will return records from the Employee table where the Salary between 27000 and 30000.

Following is the output of the above SQL query.

Logical Operators in SQL Server

Logical OR Operator:

Logical OR operator compares two Booleans as expression and returns TRUE when either of the conditions is TRUE and returns FALSE when both are FALSE. Otherwise, returns UNKNOWN (an operator that has one or two NULL expressions returns UNKNOWN).

If we want to select rows that satisfy at least one of the given conditions, then we can use the logical OP operator.

For example: if we want to find the employees whose Department either IT or HR, then the query would be like,

SELECT *
FROM Employee 
WHERE Department = 'IT' OR Department = 'HR'

The above SQL statement will return records from the Employee table where the Department is either IT or HR.

Following is the output of the above SQL query.

Logical Operators in SQL Server

Logical NOT Operator:

Logical NOT takes a single Boolean as an argument and changes its value from false to true or from true to false.

If we want to select rows that do not satisfy a condition, then we can use the logical NOT operator. NOT results in the reverse of a condition. That is, if a condition is satisfied, then the row is not returned.

For example: If we want to find out the Employees who do not belong to the City Mumbai, then the query would be like

SELECT *
FROM Employee 
WHERE NOT CITY = 'Mumbai'

The above SQL statement will return records from the Employee table where the CITY NOT belongs to Mumbai.

Following is the output of the above SQL query.

Logical Operators in SQL Server

Nested Logical Operators:

We can use multiple logical operators in a single SQL statement. When we combine the logical operators in a SELECT statement, the order in which the statement is processed is

  1. NOT 
  2. AND 
  3. OR 

For example: If we want to select the employees who Salary is between 27000 and 30000, or those whose City is not Mumbai, then the query would be like,

SELECT *
FROM Employee 
WHERE Salary >= 27000 AND Salary <= 30000
OR NOT CITY = 'Mumbai'

Following is the output of the above SQL query.

Logical Operators in SQL Server

In this case, the filter works as follows:

First, all the Employees who do not belong to the City Mumbai are selected.

Second, all the Employees, whose Salary between 27000 and 30000 are selected.

And finally, the result is the rows which satisfy at least one of the above conditions is returned.

NOTE: The order of the condition is important, if the order changes we may get a different result.

In the next article, I will discuss the IN, BETWEEN and LIKE Operators in SQL Server.

SUMMARY

In this article, I try to explain the Logical Operators in SQL Server step by step with some examples. I hope this article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.

Leave a Reply

Your email address will not be published. Required fields are marked *