SQL Logical Operators: AND, OR, and NOT
SQL Tutorial
-
SQL SyntaxSQL Syntax
-
SQL ORDER BY ClauseSQL ORDER BY Clause
-
Introduction to SQLIntroduction to SQL
-
SQL SELECT DISTINCTSQL SELECT DISTINCT
-
SQL Logical OperatorsSQL Logical Operators
SQL Logical Operators: AND, OR, and NOT
Logical operators in SQL are used to combine multiple conditions in a WHERE clause to filter data more precisely. The primary logical operators are AND, OR, and NOT.
SQL AND Operator
The AND operator is used to combine multiple conditions where all conditions must be true for a record to be included in the result set.
Syntax:
SELECT column1, column2, …
FROM table_name
WHERE condition1 AND condition2;
Examples:
- Retrieve employees who are in the ‘IT’ department and have a salary greater than 60,000:
SELECT employee_id, first_name, last_name, department, salary
FROM employees
WHERE department = ‘IT’ AND salary > 60000;
Output:
employee_id | first_name | last_name | department | salary |
1 | Rajesh | Singh | IT | 65000 |
3 | Priya | Verma | IT | 70000 |
- Find employees who are in ‘Finance’ and are older than 35:
SELECT employee_id, first_name, last_name, department, age
FROM employees
WHERE department = ‘Finance’ AND age > 35;
Output:
employee_id | first_name | last_name | department | age |
2 | Aarti | Mehta | Finance | 40 |
4 | Ravi | Kumar | Finance | 38 |
- Get details of employees who are in the ‘Marketing’ department and have been with the company for more than 5 years:
SELECT employee_id, first_name, last_name, department, years_with_company
FROM employees
WHERE department = ‘Marketing’ AND years_with_company > 5;
Output:
employee_id | first_name | last_name | department | years_with_company |
5 | Anil | Kapoor | Marketing | 7 |
6 | Neha | Gupta | Marketing | 6 |
SQL OR Operator
The OR operator is used to combine multiple conditions where at least one of the conditions must be true for a record to be included in the result set.
Syntax:
SELECT column1, column2, …
FROM table_name
WHERE condition1 OR condition2;
Examples:
- Retrieve employees who are either in the ‘HR’ department or have a salary less than 50,000:
SELECT employee_id, first_name, last_name, department, salary
FROM employees
WHERE department = ‘HR’ OR salary < 50000;
Output:
employee_id | first_name | last_name | department | salary |
4 | Ravi | Kumar | HR | 70000 |
5 | Anil | Kapoor | Marketing | 45000 |
6 | Neha | Gupta | Marketing | 48000 |
- Find employees who are in ‘Sales’ or whose age is less than 30:
SELECT employee_id, first_name, last_name, department, age
FROM employees
WHERE department = ‘Sales’ OR age < 30;
Output:
employee_id | first_name | last_name | department | age |
1 | Rajesh | Singh | Sales | 28 |
2 | Aarti | Mehta | Marketing | 29 |
3 | Priya | Verma | IT | 30 |
- Get employees who are in ‘Finance’ or have a salary greater than 60,000:
SELECT employee_id, first_name, last_name, department, salary
FROM employees
WHERE department = ‘Finance’ OR salary > 60000;
Output:
employee_id | first_name | last_name | department | salary |
2 | Aarti | Mehta | Finance | 65000 |
3 | Priya | Verma | IT | 70000 |
4 | Ravi | Kumar | HR | 70000 |
SQL NOT Operator
The NOT operator is used to negate a condition. It returns records where the specified condition is false.
Syntax:
SELECT column1, column2, …
FROM table_name
WHERE NOT condition;
Examples:
- Retrieve employees who are not in the ‘Sales’ department:
SELECT employee_id, first_name, last_name, department
FROM employees
WHERE NOT department = ‘Sales’;
Output:
employee_id | first_name | last_name | department |
2 | Aarti | Mehta | Marketing |
4 | Ravi | Kumar | HR |
5 | Anil | Kapoor | Marketing |
- Find employees whose age is not 30:
SELECT employee_id, first_name, last_name, age
FROM employees
WHERE NOT age = 30;
Output:
employee_id | first_name | last_name | age |
1 | Rajesh | Singh | 28 |
2 | Aarti | Mehta | 29 |
4 | Ravi | Kumar | 38 |
5 | Anil | Kapoor | 29 |
- Get details of employees who do not have a salary greater than 50,000:
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE NOT salary > 50000;
Output:
employee_id | first_name | last_name | salary |
1 | Rajesh | Singh | 45000 |
5 | Anil | Kapoor | 45000 |
6 | Neha | Gupta | 48000 |
Tips to Remember
- Parentheses for Clarity: Use parentheses to group conditions and clarify logic, especially when combining multiple operators.
- Order of Evaluation: SQL evaluates conditions from left to right. AND has higher precedence than OR. Use parentheses to control evaluation order.
- Combine Thoughtfully: Logical operators help in forming complex queries. Use them carefully to avoid overly complicated queries.
- Test Queries: Always test your queries with different conditions to ensure they return the expected results.
Power of Logical Operators
Logical operators AND, OR, and NOT help in:
- Refining Query Results: Combining multiple conditions to retrieve precise data.
- Handling Complex Filters: Creating complex queries to meet specific data retrieval needs.
- Negating Conditions: Excluding data that doesn’t meet the criteria.
FAQ
Q1: Can I use AND and OR together in a query?
A1: Yes, you can use AND and OR together. Use parentheses to group conditions and control the order of evaluation.
Q2: How do I use NOT with multiple conditions?
A2: Combine NOT with AND or OR to negate multiple conditions. For example:
SELECT employee_id, first_name
FROM employees
WHERE NOT (department = ‘Sales’ AND salary > 50000);
Q3: Can NOT be used with NULL values?
A3: Yes, but remember that IS NOT NULL is used to filter out NULL values, and NOT NULL is used in conditions.
Q4: How does SQL handle precedence of logical operators?
A4: SQL evaluates AND conditions before OR conditions. Use parentheses to override this default behavior if needed.
Q5: Can I use logical operators in combination with aggregate functions?
A5: Yes, but remember that logical operators are used in the WHERE clause before aggregation. Use HAVING for conditions after aggregation.
