In SQL Server, you can use multiple WHERE conditions to filter the results of a SELECT query based on specific criteria. This allows you to retrieve data that meets multiple requirements simultaneously.
To use multiple WHERE conditions in an SQL Server SELECT query, you can combine conditions using logical operators such as AND, OR, and NOT. These operators allow you to refine your queries to fetch data that meets specific criteria.
Basic Structure of a WHERE Clause
The basic syntax for a WHERE clause in a SELECT statement is:
SELECT column1, column2, ... FROM table_name WHERE condition1 AND/OR/NOT condition2;
Examples of Multiple WHERE Conditions
1. Using AND
The AND operator is used to ensure that all the conditions must be true for the rows to be selected.
SELECT * FROM Employees WHERE Department = 'Sales' AND Salary > 50000;
In this example, only employees who work in the Sales department and have a salary greater than 50,000 will be selected.
2. Using OR
The OR operator is used when at least one of the conditions must be true for the rows to be selected.
SELECT * FROM Employees WHERE Department = 'Sales' OR Department = 'Marketing';
This query selects employees who work in either the Sales or the Marketing department.
3. Combining AND and OR
You can combine both AND and OR operators in a single query. Use parentheses to group conditions to ensure the correct order of evaluation.
SELECT * FROM Employees WHERE (Department = 'Sales' OR Department = 'Marketing') AND Salary > 50000;
In this example, employees who work in either Sales or Marketing and have a salary greater than 50,000 will be selected.
4. Using NOT
The NOT operator is used to select rows where a condition is not true.
SELECT * FROM Employees WHERE NOT Department = 'Sales';
This query selects all employees who are not in the Sales department.
Additional Considerations
Order of Evaluation: AND has higher precedence than OR, meaning AND conditions are evaluated first unless parentheses are used to specify the order.
NULL Values: SQL conditions involving NULL values can behave unexpectedly. Use IS NULL or IS NOT NULL to explicitly handle NULL values.
Boolean Logic: Understand Boolean logic (AND, OR, NOT) to effectively combine conditions.
Data Types: Ensure that the data types of values used in conditions match the corresponding column types.
Case Sensitivity: Be aware of case sensitivity in comparisons. You can use functions like UPPER or LOWER to make comparisons case-insensitive.
Complex Conditions: For complex filtering, consider using subqueries, joins, or CASE expressions.
By using these logical operators effectively, you can create complex queries that retrieve exactly the data you need.