Logical Operators in SQL Server

Back to: SQL Server Tutorial For Beginners and Professionals

Logical Operators in SQL Server with Examples

In this article, I am going to discuss the Logical Operators in SQL Server with Examples. Please read our previous article where we discussed the Comparison Operators with examples. At the end of this article, you will understand what are logical operators and why we need and the different types of logical operators in SQL Server with examples.

What are Logical Operators in SQL Server?

The Logical Operators in SQL Server 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.

Logical Operators Example in SQL Server:

Let us understand how to use SQL Server Logical Operators with some examples. We are going to use the following Employee table to understand the Logical Operators.

Please use the below script to create the 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

Logical AND Operator in SQL Server:

The Logical AND operator in SQL Server compares two Booleans expressions 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 you want to select rows that must satisfy all the given conditions, then you need to use the logical AND operator. For example, if you want to find the employees from the Employee table where the salary between 27000 and 30000, then you need to use the AND operator as shown in the below query.

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

Following is the output of the above SQL query.

Logical OR Operator in SQL Server:

The Logical OR Operator is used to compare two Booleans expressions and returns TRUE when either of the conditions is TRUE and returns FALSE when both of the conditions are FALSE. Otherwise, returns UNKNOWN (an operator that has one or two NULL expressions returns UNKNOWN).

If you want to select rows that satisfy at least one of the given conditions, then you need to use the logical OR operator. For example, if you want to fetch the employees whose Department is either IT or HR, then you need to use the OR operator as shown in the below SQL query.

SELECT * FROM Employee WHERE Department = ‘IT’ OR Department = ‘HR’

Following is the output of the above SQL query.

Logical NOT Operator in SQL Server:

The Logical NOT Operator 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 you need to 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 you want to fetch the Employees who do not belong to the City Mumbai, then you need to use the NOT Operator as shown below.

SELECT * FROM Employee WHERE NOT CITY = ‘Mumbai’

Following is the output of the above SQL query.

Nested Logical Operators in SQL Server:

We can also 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 

Nested Logical Operators Example:

If we want to select the employees whose 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.

In this case, the filter works as follows:

1. First, all the Employees who do not belong to the City Mumbai are selected.
2. Second, all the Employees, whose Salary between 27000 and 30000 are selected.
3. And finally, the result is the rows that satisfy at least one of the above conditions are returned.

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

In the next article, I am going to discuss the IN, BETWEEN, and LIKE Operators in SQL Server. Here, in this article, I try to explain the Logical Operators in SQL Server with Examples. I hope this article will help you with your needs. I would like to have your feedback. Please post your feedback, question, or comments about this article.