Logical Operators in SQL

Logical operators in SQL are operators used to combine or negate one or more conditions in an SQL expression, allowing you to create complex logical conditions for filtering and evaluating data.

In simple words, logical operators allow you to check more than one condition at the same time. For example, suppose an employees table contains employee information. You want to find employees who work in the IT department and have a salary greater than ₹50,000.

A single condition can check the department:

SELECT *
FROM employees
WHERE department = 'IT';

But if you want to apply another condition to the same query, you need to use the AND logical operator:

SELECT *
FROM employees
WHERE department = 'IT' AND salary > 50000;

Here, SQL checks both conditions using short-circuit evaluation and returns only employees who satisfy both conditions.

Types of Logical Operators in SQL

SQL provides three fundamental logical operators:

• AND
• OR
• NOT

Before jumping to understand the SQL AND operator, let’s set up a sample database and table. We’ll use this example table throughout the tutorial to practice our queries!

Create Database and Table in MySQL

Step 1: Create a Database

The following SQL query creates a database named company_db.

CREATE DATABASE company_db;

In this query:

• CREATE DATABASE is an SQL statement used to create a new database.
• company_db is the name we have given to the database.
• The semicolon ; marks the end of the SQL statement.

After executing this statement, the database company_db is created.

Step 2: Select the Database

Creating a database does not automatically make it the active database for subsequent SQL statements. We need to select it. To select the database, type the following SQL query:

USE company_db;

In this SQL query:

• USE tells MySQL which database you want to work with. It is a MySQL statement that selects a database for the current session.
• company_db is the database we created.
• After executing this statement, subsequent table-related statements are executed in the selected database.

Step 3: Create a Table in MySQL Database

Now we can create an employees table inside company_db. The following SQL query creates a table named employees in the database:

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(50),
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

In this SQL query:

• CREATE TABLE employees creates a new table named employees.
• employee_id INT creates an integer column for the employee’s ID.
• PRIMARY KEY makes employee_id the unique identifier for each employee.
• name VARCHAR(50) stores the employee’s name.
• department VARCHAR(50) stores the employee’s department.
• salary DECIMAL(10, 2) stores salary values with up to 10 total digits and 2 digits after the decimal point.

Step 4: Insert Sample Data

Now that the table exists, we insert some records into the table. The following SQL query inserts sample data into the table.

INSERT INTO employees (employee_id, name, department, salary)
VALUES
    (101, 'Amit', 'IT', 60000.00),
    (102, 'Neha', 'HR', 55000.00),
    (103, 'Rahul', 'IT', 45000.00),
    (104, 'Priya', 'Sales', 70000.00);

This inserts four employees into the table.

Step 5: View the Data

To retrieve all rows and columns from the table, run the following query:

SELECT * FROM employees;

Expected Output

Now our sample database is ready.

AND Operator in SQL

The AND operator in SQL is a logical operator used to combine two or more conditions. A row satisfies an AND expression only when all the specified conditions evaluate to TRUE.

The general syntax to combine two or more conditions using the AND operator at the same time is

SELECT column1, column2
FROM table_name
WHERE condition1 AND condition2;

You can also combine more than two conditions:

SELECT column1, column2
FROM table_name
WHERE condition1
AND condition2
AND condition3;

Example 1: AND Operator with Two Conditions

Suppose we want to find employees who work in the IT department and earn more than ₹50,000.

SELECT employee_id, name, department, salary
FROM employees
WHERE department = 'IT'
  AND salary > 50000;

Expected Output

In the above SQL query:

• SELECT employee_id, name, department, salary specifies the columns to display in the result set.
• FROM employees tells SQL to retrieve data from the employees table.
• WHERE begins the filtering condition to restrict which rows are returned.
• department = 'IT' checks whether the employee belongs to the IT department.
• AND is the logical operator that connects the conditions and requires both to be true.
• salary > 50000 checks whether the employee’s salary is greater than ₹50,000.
• SQL returns only rows where both conditions are satisfied.

How AND Works

Consider the four possible combinations of two conditions:

Therefore, with the AND operator, both conditions must be true for the complete expression to be TRUE.

OR Operator in SQL

The OR operator in SQL is a logical operator used to combine two or more conditions when you want a row to be selected if at least one condition evaluates to TRUE. The general syntax to combine two or more conditions using the OR operator is:

SELECT column1, column2
FROM table_name
WHERE condition1
   OR condition2;

When you use the OR operator, a row satisfies an OR expression when at least one of the specified conditions evaluates to TRUE.

Example 2: OR Operator with Two Conditions

Suppose we want to find employees who work in either the IT department or the HR department. Run the following SQL query for this:

SELECT employee_id, name, department, salary
FROM employees
WHERE department = 'IT'
   OR department = 'HR';

Expected Output

In the above query example:

• SELECT employee_id, name, department, salary specifies the exact columns you want to display in the result set.
• FROM employees tells SQL to retrieve the data from the employees table.
• WHERE introduces the filtering condition to restrict which rows are returned.
• department = 'IT' checks whether an employee belongs to the IT department.
• OR is the logical operator that connects the conditions and requires one of the specified conditions to be true.
• department = 'HR' checks whether an employee belongs to the HR department.
• A row is returned if either condition is met.

How OR Works

For two or more conditions, at least one condition must be true for the OR expression to be true.

Unlike AND, where both conditions must be true, OR requires only one or more conditions to be true.

Example 3: OR Operator with Three Conditions

Suppose we want employees from the IT, HR, or Sales departments. We will run the following query:

SELECT employee_id, name, department, salary
FROM employees
WHERE department = 'IT'
   OR department = 'HR'
   OR department = 'Sales';

Expected Output

NOT Operator in SQL

The NOT operator is a logical operator used to negate (reverse) the logical result of a condition. In simple words, NOT tells SQL return rows for which the specified condition is not true. The general syntax is given below:

SELECT column1, column2
FROM table_name
WHERE NOT condition;

Example 4: NOT Operator

SELECT employee_id, name, department
FROM employees
WHERE NOT department = 'HR';

This query tells SQL to return employees whose department is not HR.

Expected Output

Neha is excluded because her department is HR.

How NOT Works

Let us consider this condition:

department = 'HR'

For each employee, SQL evaluates whether the condition is true or false.

Example 5: NOT With a Comparison Condition

Suppose we want to find employees whose salary is not greater than ₹50,000. In this case, we will run the following query:

SELECT employee_id, name, salary
FROM employees
WHERE NOT salary > 50000;

Expected Output

For a simple comparison like this, an equivalent query is:

SELECT employee_id, name, salary
FROM employees
WHERE salary <= 50000;

Example 6: NOT With Parentheses

Suppose we want employees who are not in the IT department. Run the following SQL query:

SELECT employee_id, name, department
FROM employees
WHERE NOT (department = 'IT');

Expected Output

Example 7: NOT With AND

You can also negate a group of conditions. Suppose we want employees who are not both in the IT department and earning more than ₹50,000.

SELECT employee_id, name, department, salary
FROM employees
WHERE NOT (
    department = 'IT'
    AND salary > 50000
);

The condition inside the parentheses is true only for Amit:

• department = ‘IT’ → TRUE
• salary > 50000 → TRUE
• TRUE AND TRUE → TRUE
• NOT TRUE → FALSE

Therefore, Amit is excluded, while the other employees are returned.

Expected Output