sql query with multiple where statements

Mastering SQL Queries with Multiple WHERE Clauses

Learn how to effectively combine multiple conditions in SQL WHERE clauses using logical operators (AND, OR, NOT) to filter data precisely. This guide covers basic to advanced techniques for MySQL and other SQL databases.

SQL's WHERE clause is fundamental for filtering records from a database table based on specified conditions. While a single condition is straightforward, real-world applications often require filtering data using multiple criteria. This article will guide you through the various ways to construct SQL queries with multiple WHERE statements, ensuring you retrieve exactly the data you need. We'll explore logical operators, parentheses for grouping, and best practices for writing efficient and readable queries.

The Basics: AND and OR Operators

The most common way to combine multiple conditions in a WHERE clause is by using the logical operators AND and OR. These operators allow you to specify how the conditions should relate to each other.

• AND Operator: Returns a record if all the conditions separated by AND are true.
• OR Operator: Returns a record if any of the conditions separated by OR are true.

SELECT column1, column2
FROM your_table
WHERE condition1 AND condition2;

SELECT column1, column2
FROM your_table
WHERE condition1 OR condition2;

Basic usage of AND and OR operators in a WHERE clause.

Combining AND and OR with Parentheses

When you need to mix AND and OR operators, parentheses () become crucial for defining the order of evaluation and ensuring your query logic is correct. Parentheses allow you to group conditions, forcing them to be evaluated as a single unit before being combined with other conditions.

Consider a scenario where you want to find customers who are either from 'New York' AND have an 'Active' status, OR are from 'California' regardless of status.

flowchart TD
    A["Start Query"] --> B{"Customer Status = 'Active'"}
    B -- Yes --> C{"Customer City = 'New York'"}
    C -- Yes --> D["Result: New York & Active"]
    B -- No --> E{"Customer City = 'California'"}
    C -- No --> E
    E -- Yes --> F["Result: California"]
    D --> G["End Query"]
    F --> G

Flowchart illustrating the logic of combined AND/OR conditions.

SELECT customer_id, customer_name, city, status
FROM customers
WHERE (city = 'New York' AND status = 'Active') OR city = 'California';

Example of using parentheses to group conditions with AND and OR.

Other Useful Operators for Filtering

Beyond AND and OR, several other operators can be used within your WHERE clauses to create powerful filtering logic:

• NOT Operator: Negates a condition. NOT condition returns true if the condition is false.
• IN Operator: Checks if a value matches any value in a list.
• BETWEEN Operator: Checks if a value is within a range.
• LIKE Operator: Searches for a specified pattern in a column.
• Comparison Operators: =, != (or <>), >, <, >=, <= for numerical and string comparisons.

SELECT product_name, price
FROM products
WHERE category_id NOT IN (1, 5) AND price BETWEEN 50.00 AND 100.00;

SELECT employee_name, email
FROM employees
WHERE employee_name LIKE 'J%' AND hire_date >= '2020-01-01';

Examples using NOT IN, BETWEEN, and LIKE operators.