Sql Logical Operators And Or Not Complete Guide

Last Update:2025-06-23T00:00:00 .NET School AI Teacher - SELECT ANY TEXT TO EXPLANATION. 8 mins read Difficulty-Level: beginner

Understanding the Core Concepts of SQL Logical Operators AND, OR, NOT

SQL Logical Operators: AND, OR, NOT

AND Operator

The AND operator is used to combine multiple conditions in a WHERE clause. For a record to meet the condition established by the AND operator, it must satisfy every individual condition specified in the WHERE clause.

Syntax:

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

Example:

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

In this example, the query will return all employees who work in the Sales department and earn more than $50,000.

OR Operator

The OR operator is used to combine multiple conditions in a WHERE clause; however, a record is returned if it satisfies at least one of the conditions.

Syntax:

SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2 OR condition3 ...;

Example:

SELECT *
FROM employees
WHERE department = 'Sales' OR salary > 50000;

In this example, the query will return all employees who either work in the Sales department or earn more than $50,000.

NOT Operator

The NOT operator reverses the result set of a condition. It is used to select records that do not match a specified condition.

Syntax:

SELECT column1, column2, ...
FROM table_name
WHERE NOT condition;

Example:

SELECT *
FROM employees
WHERE NOT department = 'Sales';

In this example, the query will return all employees who do not work in the Sales department.

Combining AND, OR, and NOT

Logical operators can be combined to create more complex queries. However, to ensure the intended logic is followed, it is essential to use parentheses to control the order of evaluation.

Example:

SELECT *
FROM employees
WHERE (department = 'Sales' AND salary > 50000) OR NOT department = 'Engineering';

In this query, the parentheses ensure that the AND condition is evaluated before the OR condition. Thus, it will return employees who meet either of the following conditions: working in Sales and earning more than $50,000, or not working in Engineering.

Important Information

AND and OR Precedence: When a WHERE clause contains both AND and OR operators, the AND operator takes precedence over OR. To ensure clarity and correctness, use parentheses to explicitly specify the precedence.
Short-Circuit Evaluation: SQL uses short-circuit evaluation, meaning that if the outcome of the condition can be determined by the first operator, SQL stops evaluating further conditions.
Combining Multiple NOT Conditions: Using multiple NOT operators in a single clause can lead to confusion; consider using IN or NOT IN to avoid complex conditions.
Performance Considerations: When using logical operators, especially with large datasets, proper indexing can significantly improve query performance. Always consider database design and structure when writing complex queries.

Online Code run

🔔 Note: Select your programming language to check or run code at

💻 Run Code Compiler

Step-by-Step Guide: How to Implement SQL Logical Operators AND, OR, NOT

Table Structure: Employees

| EmployeeID | FirstName | LastName | Department | Salary | |------------|------------|----------|--------------|--------| | 1 | John | Doe | Sales | 50000 | | 2 | Jane | Smith | Marketing | 60000 | | 3 | Michael | Johnson | IT | 75000 | | 4 | Emily | Davis | Sales | 55000 | | 5 | David | Wilson | Marketing | 62000 | | 6 | Sarah | Brown | Finance | 80000 |

Example 1: Using the `AND` Operator

Goal: Find all employees who work in the Sales department with a salary greater than 50,000.

SQL Query:

SELECT EmployeeID, FirstName, LastName, Department, Salary
FROM Employees
WHERE Department = 'Sales' AND Salary > 50000;

Step-by-Step Explanation:

The SELECT statement specifies the columns we want to retrieve.
The FROM clause specifies the Employees table as the source of data.
The WHERE clause includes two conditions:
- Department = 'Sales': This condition checks if the employee is in the Sales department.
- Salary > 50000: This condition checks if the employee's salary is greater than 50,000.
The AND operator ensures that both conditions must be true for an employee to be included in the result.

Expected Result: | EmployeeID | FirstName | LastName | Department | Salary | |------------|-----------|----------|------------|--------| | 4 | Emily | Davis | Sales | 55000 |

Example 2: Using the `OR` Operator

Goal: Find all employees who work in either the Marketing department or have a salary greater than 60,000.

SQL Query:

SELECT EmployeeID, FirstName, LastName, Department, Salary
FROM Employees
WHERE Department = 'Marketing' OR Salary > 60000;

Step-by-Step Explanation:

The SELECT statement specifies the columns we want to retrieve.
The FROM clause specifies the Employees table as the source of data.
The WHERE clause includes two conditions:
- Department = 'Marketing': This condition checks if the employee is in the Marketing department.
- Salary > 60000: This condition checks if the employee's salary is greater than 60,000.
The OR operator ensures that if either condition is true, the employee will be included in the result.

Expected Result: | EmployeeID | FirstName | LastName | Department | Salary | |------------|-----------|----------|-------------|--------| | 2 | Jane | Smith | Marketing | 60000 | | 5 | David | Wilson | Marketing | 62000 | | 6 | Sarah | Brown | Finance | 80000 |

Example 3: Using the `NOT` Operator

Goal: Find all employees who do not work in the IT department.

SQL Query:

SELECT EmployeeID, FirstName, LastName, Department, Salary
FROM Employees
WHERE NOT Department = 'IT';

Step-by-Step Explanation:

The SELECT statement specifies the columns we want to retrieve.
The FROM clause specifies the Employees table as the source of data.
The WHERE clause includes one condition:
- Department = 'IT': This condition checks if the employee is in the IT department.
The NOT operator negates the condition, so it includes employees who are not in the IT department.

Expected Result: | EmployeeID | FirstName | LastName | Department | Salary | |------------|-----------|----------|-------------|--------| | 1 | John | Doe | Sales | 50000 | | 2 | Jane | Smith | Marketing | 60000 | | 4 | Emily | Davis | Sales | 55000 | | 5 | David | Wilson | Marketing | 62000 | | 6 | Sarah | Brown | Finance | 80000 |

Example 4: Combining `AND`, `OR`, and `NOT`

Goal: Find all employees who work in either the Sales or Marketing department and do not have a salary of 60,000.

SQL Query:

SELECT EmployeeID, FirstName, LastName, Department, Salary
FROM Employees
WHERE (Department = 'Sales' OR Department = 'Marketing') AND NOT Salary = 60000;

Step-by-Step Explanation:

The SELECT statement specifies the columns we want to retrieve.
The FROM clause specifies the Employees table as the source of data.
The WHERE clause includes two main conditions combined with the AND operator:
- (Department = 'Sales' OR Department = 'Marketing'): This sub-condition checks if the employee is in either the Sales or Marketing department.
- NOT Salary = 60000: This sub-condition checks if the employee's salary is not 60,000.
The parentheses () are used to ensure that the OR condition is evaluated first before applying the AND condition.

Expected Result: | EmployeeID | FirstName | LastName | Department | Salary | |------------|-----------|----------|-------------|--------| | 1 | John | Doe | Sales | 50000 | | 4 | Emily | Davis | Sales | 55000 | | 5 | David | Wilson | Marketing | 62000 |

Sql Logical Operators And Or Not Complete Guide