How to Write Multiple Where Conditions in SQL
In SQL, the WHERE clause is used to filter records based on specific conditions. When you need to apply multiple conditions to filter the data, you can use multiple WHERE conditions. This article will guide you on how to write multiple WHERE conditions in SQL and provide some practical examples to illustrate the process.
Using Logical Operators
To write multiple WHERE conditions in SQL, you can use logical operators such as AND, OR, and NOT. These operators allow you to combine multiple conditions and specify how they should be evaluated.
AND Operator
The AND operator is used to combine two or more conditions, and it returns true if all the conditions are true. When using the AND operator, you need to separate each condition with a comma.
For example, consider a table named “Employees” with columns “Name”, “Age”, and “Department”. To retrieve all employees who are over 30 years old and work in the “HR” department, you can use the following SQL query:
“`sql
SELECT FROM Employees
WHERE Age > 30 AND Department = ‘HR’;
“`
OR Operator
The OR operator is used to combine two or more conditions, and it returns true if at least one of the conditions is true. Similar to the AND operator, you need to separate each condition with a comma when using the OR operator.
Continuing with the “Employees” table, if you want to retrieve all employees who are either over 30 years old or work in the “HR” department, you can use the following SQL query:
“`sql
SELECT FROM Employees
WHERE Age > 30 OR Department = ‘HR’;
“`
NOT Operator
The NOT operator is used to negate a condition. It returns true if the condition is false. You can use the NOT operator with the AND and OR operators to create more complex queries.
For instance, if you want to retrieve all employees who are not over 30 years old and do not work in the “HR” department, you can use the following SQL query:
“`sql
SELECT FROM Employees
WHERE NOT (Age > 30 AND Department = ‘HR’);
“`
Order of Evaluation
When using multiple WHERE conditions, it’s important to understand the order of evaluation. In SQL, the logical operators AND and OR have a specific order of precedence. AND has a higher precedence than OR, which means that conditions using AND are evaluated before conditions using OR.
To change the order of evaluation, you can use parentheses. For example, consider the following query:
“`sql
SELECT FROM Employees
WHERE Age > 30 OR Department = ‘HR’ AND Age < 40;
```
In this query, the AND condition is evaluated first, so the result will be all employees who are either over 30 years old and work in the "HR" department or under 40 years old.
Conclusion
Writing multiple WHERE conditions in SQL is a fundamental skill that allows you to filter data based on various criteria. By using logical operators like AND, OR, and NOT, you can create complex queries to retrieve the data you need. Remember to consider the order of evaluation and use parentheses when necessary. With practice, you’ll become proficient in writing multiple WHERE conditions in SQL.
