How to Have Multiple WHERE Conditions in a Single Query in SQL | A Comprehensive Guide

The WHERE clause is one of the most useful parts of an SQL query, allowing you to filter records and narrow down your results. While simple queries may only need a single condition, real-world scenarios often demand more complex filtering logic with multiple conditions. 

This guide will explore various techniques for constructing SQL queries with multiple WHERE conditions.

Using AND AND Operator for Precise Filtering

The AND operator lets you filter rows that match all the specified conditions.

SELECT * 
FROM customers
WHERE age > 30 AND country = 'USA'

This returns customers who are over 30 and located in the USA.

Using OR Operator for Inclusive Filtering

The OR operator includes rows that match any of the conditions.

SELECT *
FROM products 
WHERE price < 20 OR category = 'electronics'

This returns cheap products or electronics.

Combining AND and OR Methods

You can mix AND and OR in a single statement:

SELECT *
FROM employees
WHERE salary > 80000 AND (dept = 'sales' OR dept = 'marketing')

This filters for well-paid sales or marketing staff.

Using NOT Operator for Exclusion

The NOT operator excludes rows that match the condition.

SELECT *
FROM applicants
WHERE NOT graduation_date IS NULL

This returns only applicants who have a graduation date.

Nesting Queries for Isolating Complex Logic

You can nest queries in the WHERE clause to isolate complex logic:

SELECT *
FROM table1
WHERE column IN (SELECT column FROM table2...)

This nests a subquery within the WHERE condition.

Subqueries in WHERE for Data Filtering

Subqueries let you filter based on the results of a separate query:

SELECT *
FROM employees
WHERE dept IN (SELECT dept_id FROM departments WHERE region = 'USA')

EXISTS Operator for Data Filtering

Check if rows returned by a subquery exist:

SELECT *
FROM orders
WHERE EXISTS (SELECT * FROM order_items, WHERE order_id = orders.id)

Inline Views for Focused Results

Filter subquery results before outer query:

SELECT *
FROM 
 (SELECT * FROM employees WHERE salary > 80000) well_paid_employees
WHERE dept = 'sales'

Derived Tables Method

Isolate complex logic in a reusable derived table:

SELECT *
FROM
 (SELECT *, 
      CASE WHEN salary > 100000 THEN 'highly paid' ELSE 'normal' END AS salary_class
  FROM employees) derived_table
WHERE salary_class = 'highly paid'

CTEs Method for Extraction

Extract query logic into a CTE for modularity:

WITH high_earners AS (
  SELECT * 
  FROM employees
  WHERE salary > 100000
)
SELECT *
FROM high_earners
WHERE dept = 'sales'

Stored Procedures for Complex Filters

Encapsulate complex filters in a reusable stored procedure:

CREATE PROCEDURE top_employees(@dept varchar(20))
AS
SELECT * 
FROM employees
WHERE dept = @dept AND salary > 100000

Temporary Tables before Filtering

Isolate subsets of data in a temp table before filtering further:

CREATE TEMP TABLE shortlisted_candidates AS
SELECT * 
FROM applicants
WHERE test_score > 90 
SELECT *
FROM shortlisted_candidates 
WHERE graduation_status = 'complete'

FAQs – Frequently Asked Questions and Answers

1. Is performance affected by multiple WHERE conditions?

Answer: Yes, especially on large datasets. Use appropriate indexes and optimized queries.

2. Can WHERE conditions call stored procedures or functions?

Answer: Absolutely, this can help modularize complex filters.

3. What’s the maximum number of WHERE conditions allowed?

Answer: There is no set limit, but hundreds of complex conditions could impact readability.

To Conclude

There are many techniques available to construct sophisticated SQL queries using multiple WHERE conditions. Combining logical operators, subqueries, reusable objects, and other database features provides endless possibilities to build powerful data filtering logic. Use these tools to query your data with precision and simplicity.