Combining Conditions with SQL AND
SQL AND operator is one of the most important logical operators used to combine two or more conditions in a query. When multiple conditions need to be true for a row to be included in the result set, the AND operator is essential. It ensures that each condition is evaluated, and only rows satisfying all conditions are returned.
This chapter covers everything you need to know about using the AND operator in SQL queries. We will explore its basic usage, advanced techniques, and practical examples to help you master how to combine multiple conditions effectively.
Basics of the SQL AND Operator
The AND operator allows you to specify multiple conditions in a WHERE clause. When combining conditions using AND, each condition must evaluate to TRUE for the row to be returned.
Syntax:
SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 AND condition3 ...;
condition1, condition2, condition3: Each condition is a logical expression that can be true or false. All conditions must be TRUE for a row to be included in the result.
Example 1: Basic AND Usage
Consider the employees table:
| ID | Name | Department | Salary |
|---|---|---|---|
| 1 | John Doe | HR | $5000 |
| 2 | Jane Smith | IT | $6000 |
| 3 | Bob Brown | IT | $7000 |
| 4 | Mary White | HR | $5000 |
| 5 | David Green | Sales | $5500 |
Query:
SELECT name, salary
FROM employees
WHERE department = 'IT' AND salary > 6000;
Output:
| Name | Salary |
|---|---|
| Bob Brown | $7000 |
- In this query, both conditions must be
TRUEfor a row to be returned:- The employee must be in the
ITdepartment. - The employee’s salary must be greater than 6000.
- The employee must be in the
- Only
Bob Brownsatisfies both conditions.
Combining Multiple Conditions
You can combine more than two conditions using the AND operator. The more conditions you combine, the more specific your query results will be.
Example 2: Multiple Conditions with AND
Let’s filter employees from the IT department with a salary greater than 6000 and an id less than 4.
Query:
SELECT name, salary
FROM employees
WHERE department = 'IT' AND salary > 6000 AND id < 4;
Output:
| Name | Salary |
|---|---|
| Bob Brown | $7000 |
- All conditions are applied:
department = 'IT'salary > 6000id < 4
Bob Brownmeets all conditions, so he is returned.
Operator Precedence with AND
In SQL, logical operators like AND and OR have precedence. The AND operator has higher precedence than OR, which means AND conditions are evaluated before OR conditions unless parentheses are used to group conditions explicitly.
Example 3: Operator Precedence
Suppose we want to retrieve employees who are either in the IT department and have a salary greater than 6000, or they work in the HR department.
Query:
SELECT name, department, salary
FROM employees
WHERE department = 'IT' AND salary > 6000 OR department = 'HR';
Output:
| Name | Department | Salary |
|---|---|---|
| John Doe | HR | $5000 |
| Bob Brown | IT | $7000 |
| Mary White | HR | $5000 |
- This query follows the precedence rules:
- The
ANDcondition (department = 'IT' AND salary > 6000) is evaluated first. - Then the result is combined with the
ORcondition (department = 'HR').
- The
- Both
HRemployees andBob Brownfrom theITdepartment are returned.
Using Parentheses to Control Precedence
To override the default precedence of logical operators, you can use parentheses to group conditions. This ensures the query evaluates conditions in the desired order.
Example 4: Grouping Conditions with Parentheses
Suppose we want employees who are in either the IT department with a salary greater than 6000 or those in the HR department.
Query:
SELECT name, department, salary
FROM employees
WHERE (department = 'IT' AND salary > 6000) OR department = 'HR';
Output:
| Name | Department | Salary |
|---|---|---|
| John Doe | HR | $5000 |
| Mary White | HR | $5000 |
| Bob Brown | IT | $7000 |
Using parentheses, we ensure that the conditions for IT are evaluated first, and then the HR employees are included.
Advanced Usage of AND with Comparison and Logical Operators
The AND operator can be used with various comparison operators (like >, <, =, !=) and logical operators (like BETWEEN, IN, LIKE, and IS NULL) to create more advanced conditions.
Using AND with BETWEEN
The BETWEEN operator is used to filter values within a specific range. You can combine this with AND to refine your query further.
Example 5: AND with BETWEEN
Let’s find employees whose salary is between 5000 and 7000 and who work in the IT department.
SELECT name, salary
FROM employees
WHERE salary BETWEEN 5000 AND 7000 AND department = 'IT';
Output:
| Name | Salary |
|---|---|
| Jane Smith | $6000 |
| Bob Brown | $7000 |
The query returns employees from the IT department with salaries between 5000 and 7000.
Using AND with IN
The IN operator allows you to specify a set of values. It can be combined with AND to filter based on multiple criteria.
Example 6: AND with IN
Let’s filter employees who are either in the IT or HR department and have a salary greater than 5000.
SELECT name, department, salary
FROM employees
WHERE department IN ('IT', 'HR') AND salary > 5000;
Output:
| Name | Department | Salary |
|---|---|---|
| Jane Smith | IT | $6000 |
| Bob Brown | IT | $7000 |
The IN operator is used to specify the IT and HR departments, and the AND operator filters for salaries greater than 5000.
Using AND with LIKE
The LIKE operator is used for pattern matching. Combining it with AND allows you to filter data more precisely based on string patterns.
Example 7: AND with LIKE
Let’s find employees whose names start with ‘J’ and who work in the IT department.
SELECT name, department
FROM employees
WHERE name LIKE 'J%' AND department = 'IT';
Output:
| Name | Department |
|---|---|
| Jane Smith | IT |
This query returns only Jane Smith, as she is the only employee in the IT department whose name starts with J.
Handling NULL Values with AND
In SQL, NULL represents missing or unknown data. When using the AND operator with NULL values, you must handle NULL conditions explicitly, as NULL is not equal to any value (including itself).
Example 8: Using AND with IS NULL
Suppose we have a modified employees table where some employees do not have a department assigned (i.e., their department is NULL).
| ID | Name | Department | Salary |
|---|---|---|---|
| 1 | John Doe | HR | $5000 |
| 2 | Jane Smith | IT | $6000 |
| 3 | Bob Brown | IT | $7000 |
| 4 | Mary White | NULL | $5000 |
| 5 | David Green | Sales | $5500 |
Let’s find employees who have a NULL department and a salary of 5000.
Query:
SELECT name, department, salary
FROM employees
WHERE department IS NULL AND salary = 5000;
Output:
| Name | Department | Salary |
|---|---|---|
| Mary White | NULL | $5000 |
The IS NULL condition is used to explicitly filter rows where the department is NULL.
The AND operator is a fundamental part of SQL that allows for the combination of multiple conditions within a query. It ensures that every condition specified is met before a row is included in the result set. Understanding how to properly use the AND operator will significantly improve your ability to filter and retrieve precise data.Throughout this chapter, we've explored the basic syntax of the AND operator, how to use it with multiple conditions, and how to control operator precedence with parentheses. Additionally, we examined how the AND operator works with other SQL operators such as BETWEEN, IN, LIKE, and IS NULL, which provides flexibility when writing complex queries. Happy coding !❤️