SQL Filtering and Sorting: Master WHERE, AND, OR, IN, BETWEEN, and ORDER BY

You're staring at a customer database with 50,000 records, and your boss just asked you to "find all customers from California or Texas who made purchases over $500 in the last quarter, sorted by purchase amount." Without the right SQL skills, you'd be scrolling through spreadsheets for hours. But with just a few key SQL clauses, you can get that answer in seconds.

This is the power of data filtering and sorting—the fundamental skills that separate data professionals from people who just happen to work with data. Every day, analysts, marketers, and business intelligence professionals use these techniques to slice through massive datasets and extract exactly the insights they need.

What you'll learn:

• How to use WHERE clauses to filter records based on specific conditions
• How to combine multiple conditions with AND, OR, and logical operators
• How to filter efficiently using IN and BETWEEN for ranges and lists
• How to sort results with ORDER BY for meaningful data presentation
• How to avoid common filtering mistakes that lead to incorrect results

Prerequisites

You should be familiar with basic SQL SELECT statements and understand what databases and tables are. If you can write SELECT * FROM table_name, you're ready to proceed.

Understanding the WHERE Clause: Your Data Filter

Think of the WHERE clause as a bouncer at an exclusive club—it decides which rows from your table get to appear in your results. Every row must pass the test you specify, or it gets rejected.

Let's work with a realistic scenario. Imagine you're analyzing an e-commerce database with a customers table that looks like this:

-- Sample customers table structure
customer_id | first_name | last_name | email                    | state | registration_date | total_spent
1          | Sarah      | Johnson   | sarah.j@email.com       | CA    | 2023-01-15       | 1250.00
2          | Mike       | Chen      | mike.chen@email.com     | TX    | 2023-02-20       | 890.50
3          | Lisa       | Rodriguez | lisa.r@email.com        | NY    | 2023-01-08       | 2100.75
4          | David      | Kim       | david.kim@email.com     | CA    | 2023-03-12       | 450.25
5          | Emma       | Wilson    | emma.w@email.com        | FL    | 2023-02-28       | 1875.00

The basic WHERE syntax is straightforward:

SELECT column1, column2, ...
FROM table_name
WHERE condition;

Let's start with a simple filter to find all customers from California:

SELECT first_name, last_name, email, total_spent
FROM customers
WHERE state = 'CA';

This returns only Sarah Johnson and David Kim—the customers where the state column exactly matches 'CA'.

Important: Notice the single quotes around 'CA'. In SQL, text values (strings) must be enclosed in single quotes, while numbers don't need quotes.

You can use various comparison operators in your WHERE conditions:

• = (equals)
• != or <> (not equals)
• > (greater than)
• < (less than)
• >= (greater than or equal to)
• <= (less than or equal to)

For example, to find customers who have spent more than $1000:

SELECT first_name, last_name, total_spent
FROM customers
WHERE total_spent > 1000;

This returns Sarah, Lisa, and Emma—anyone with total_spent greater than 1000.

Combining Conditions with AND and OR

Real-world data questions rarely involve just one condition. You'll often need to combine multiple criteria, and this is where AND and OR operators become essential.

The AND Operator: All Conditions Must Be True

AND is like a strict teacher—every condition must be satisfied for a row to pass through. Let's find customers from California who have spent more than $1000:

SELECT first_name, last_name, state, total_spent
FROM customers
WHERE state = 'CA' AND total_spent > 1000;

This returns only Sarah Johnson, because she's the only customer who meets both criteria: living in CA AND having spent more than $1000.

You can chain multiple AND conditions together:

SELECT first_name, last_name, email
FROM customers
WHERE state = 'CA' 
  AND total_spent > 500 
  AND registration_date >= '2023-02-01';

This finds California customers who spent more than $500 and registered after February 1st, 2023.

The OR Operator: Any Condition Can Be True

OR is more lenient—if any of the conditions is true, the row gets included. Let's find customers from either California or Texas:

SELECT first_name, last_name, state, total_spent
FROM customers
WHERE state = 'CA' OR state = 'TX';

This returns Sarah, Mike, and David—anyone living in either California or Texas.

Combining AND and OR: Use Parentheses Wisely

Here's where many beginners stumble. When you mix AND and OR operators, you need parentheses to control the order of operations, just like in math.

Let's say you want customers from California or Texas, but only if they've spent more than $800:

-- WRONG - This doesn't do what you think it does
SELECT first_name, last_name, state, total_spent
FROM customers
WHERE state = 'CA' OR state = 'TX' AND total_spent > 800;

This query is interpreted as "customers from CA, OR customers from TX who spent more than $800." It would return all California customers regardless of spending, plus only high-spending Texas customers.

-- CORRECT - Use parentheses to group the OR conditions
SELECT first_name, last_name, state, total_spent
FROM customers
WHERE (state = 'CA' OR state = 'TX') AND total_spent > 800;

Now it correctly finds customers from either state, but only those who spent more than $800.

Rule of thumb: When mixing AND and OR, always use parentheses to make your intention clear, even if they're technically not needed. Your future self will thank you.

Efficient Filtering with IN and BETWEEN

While you can chain multiple OR conditions together, SQL provides more elegant solutions for common filtering patterns.

The IN Operator: Testing Against Multiple Values

Instead of writing state = 'CA' OR state = 'TX' OR state = 'NY' OR state = 'FL', you can use the IN operator:

SELECT first_name, last_name, state, total_spent
FROM customers
WHERE state IN ('CA', 'TX', 'NY', 'FL');

This is much cleaner and performs better with large lists. The IN operator checks if the column value matches any value in the provided list.

You can also use NOT IN to exclude specific values:

SELECT first_name, last_name, state
FROM customers
WHERE state NOT IN ('CA', 'TX');

This returns all customers except those from California and Texas.

IN is particularly powerful when working with subqueries or when you have a long list of values to check against. For instance, if you have a separate list of preferred customer IDs:

SELECT first_name, last_name, email
FROM customers
WHERE customer_id IN (1, 3, 5, 7, 11, 13, 17);

The BETWEEN Operator: Filtering Ranges

When you need to filter numeric ranges or dates, BETWEEN is your friend. Instead of writing:

-- The verbose way
SELECT first_name, last_name, total_spent
FROM customers
WHERE total_spent >= 500 AND total_spent <= 1500;

You can write:

-- The elegant way
SELECT first_name, last_name, total_spent
FROM customers
WHERE total_spent BETWEEN 500 AND 1500;

BETWEEN is inclusive on both ends, meaning it includes the boundary values (500 and 1500 in this case).

BETWEEN works excellently with dates too:

SELECT first_name, last_name, registration_date
FROM customers
WHERE registration_date BETWEEN '2023-02-01' AND '2023-02-28';

This finds all customers who registered during February 2023.

You can also use NOT BETWEEN to exclude a range:

SELECT first_name, last_name, total_spent
FROM customers
WHERE total_spent NOT BETWEEN 800 AND 1200;

This returns customers who spent either less than $800 or more than $1200.

Sorting Results with ORDER BY

Having the right data is only half the battle—you need to present it in a meaningful order. The ORDER BY clause sorts your results, making patterns and insights much easier to spot.

Basic Sorting

The basic ORDER BY syntax sorts results in ascending order (lowest to highest):

SELECT first_name, last_name, total_spent
FROM customers
WHERE state = 'CA'
ORDER BY total_spent;

This shows California customers sorted from lowest spender to highest spender.

To sort in descending order (highest to lowest), add DESC:

SELECT first_name, last_name, total_spent
FROM customers
WHERE total_spent > 1000
ORDER BY total_spent DESC;

This shows high-value customers with the biggest spenders first.

Multi-Column Sorting

Often, you'll want to sort by multiple criteria. List them in order of importance:

SELECT first_name, last_name, state, total_spent
FROM customers
ORDER BY state, total_spent DESC;

This first groups customers by state (alphabetically), then within each state, sorts by total_spent from highest to lowest. So you might see:

David Kim, CA, $450.25
Sarah Johnson, CA, $1,250.00
Emma Wilson, FL, $1,875.00
Lisa Rodriguez, NY, $2,100.75
Mike Chen, TX, $890.50

You can mix ASC and DESC for different columns:

SELECT first_name, last_name, state, registration_date
FROM customers
ORDER BY state ASC, registration_date DESC;

This sorts by state alphabetically, but shows the most recent registrations first within each state.

Sorting by Calculated Values

You can also sort by expressions or calculated values:

SELECT first_name, last_name, total_spent,
       total_spent / 12 as monthly_average
FROM customers
WHERE total_spent > 1000
ORDER BY total_spent / 12 DESC;

This calculates an estimated monthly average spending and sorts by that calculated value.

Pattern Matching with LIKE

Sometimes you need to find data that matches a pattern rather than an exact value. The LIKE operator, combined with wildcards, lets you do flexible text searching.

The two main wildcards are:

• % matches any sequence of characters (including zero characters)
• _ matches exactly one character

Find all customers whose first name starts with 'S':

SELECT first_name, last_name, email
FROM customers
WHERE first_name LIKE 'S%';

Find customers with Gmail addresses:

SELECT first_name, last_name, email
FROM customers
WHERE email LIKE '%@gmail.com';

Find customers whose last name has exactly 4 characters:

SELECT first_name, last_name
FROM customers
WHERE last_name LIKE '____';  -- Four underscores

Performance note: LIKE queries, especially those starting with %, can be slow on large datasets because they can't use indexes effectively. Use them judiciously.

Hands-On Exercise

Let's put it all together with a comprehensive exercise. Imagine you're analyzing an online bookstore's customer data to create a targeted marketing campaign.

Given this customers table:

CREATE TABLE customers (
    customer_id INTEGER,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    state VARCHAR(2),
    registration_date DATE,
    total_spent DECIMAL(10,2),
    books_purchased INTEGER,
    preferred_genre VARCHAR(50)
);

Write queries to answer these business questions:

1. High-value customers: Find customers who have spent more than $500 and purchased at least 10 books, sorted by total spent (highest first).

2. Regional targeting: Find customers from New York, California, or Texas who prefer either 'Mystery' or 'Science Fiction' genres.

3. Recent big spenders: Find customers who registered in 2023 and spent between $200 and $1000, sorted by registration date (most recent first).

4. Email campaign list: Find customers whose email addresses end with '.edu' or '.org', excluding those who have spent less than $100.

Here are the solutions:

-- Solution 1: High-value customers
SELECT first_name, last_name, total_spent, books_purchased
FROM customers
WHERE total_spent > 500 AND books_purchased >= 10
ORDER BY total_spent DESC;

-- Solution 2: Regional targeting
SELECT first_name, last_name, state, preferred_genre
FROM customers
WHERE state IN ('NY', 'CA', 'TX') 
  AND preferred_genre IN ('Mystery', 'Science Fiction');

-- Solution 3: Recent big spenders
SELECT first_name, last_name, registration_date, total_spent
FROM customers
WHERE registration_date >= '2023-01-01' 
  AND total_spent BETWEEN 200 AND 1000
ORDER BY registration_date DESC;

-- Solution 4: Email campaign list
SELECT first_name, last_name, email, total_spent
FROM customers
WHERE (email LIKE '%.edu' OR email LIKE '%.org') 
  AND total_spent >= 100;

Try running these queries and experiment with variations. What happens if you change the conditions? Can you make the queries more or less restrictive?

Common Mistakes & Troubleshooting

Mistake #1: Forgetting Quotes Around Text Values

-- WRONG
WHERE state = CA

-- CORRECT
WHERE state = 'CA'

Always wrap text values in single quotes. Numbers don't need quotes.

Mistake #2: Incorrect AND/OR Logic

-- WRONG: This finds CA customers OR everyone who spent >1000
WHERE state = 'CA' OR total_spent > 1000

-- LIKELY INTENDED: CA customers who spent >1000
WHERE state = 'CA' AND total_spent > 1000

-- OR MAYBE: Customers from CA, or anyone who spent >1000
WHERE state = 'CA' OR total_spent > 1000  -- This is actually correct for this intention

Always think through your logic carefully. Draw it out if needed.

Mistake #3: BETWEEN Boundary Confusion

Remember that BETWEEN is inclusive:

WHERE total_spent BETWEEN 100 AND 200
-- This includes customers who spent exactly $100.00 and exactly $200.00

If you want to exclude boundaries, use comparison operators:

WHERE total_spent > 100 AND total_spent < 200

Mistake #4: Case Sensitivity Issues

Depending on your database system, text comparisons might be case-sensitive:

-- Might miss records with 'ca' or 'Ca'
WHERE state = 'CA'

-- More robust approach
WHERE UPPER(state) = 'CA'
-- or
WHERE state ILIKE 'ca'  -- PostgreSQL case-insensitive LIKE

Mistake #5: NULL Value Surprises

NULL values can cause unexpected results. If a customer has NULL in their state field:

WHERE state = 'CA'     -- Won't match NULL
WHERE state != 'CA'    -- Won't match NULL either!
WHERE state IS NULL    -- This is how you find NULLs
WHERE state IS NOT NULL -- This excludes NULLs

Summary & Next Steps

You've now mastered the core tools for filtering and sorting data in SQL. These skills form the foundation of almost every data analysis task you'll encounter. Here's what you can now do:

• Use WHERE clauses to filter records based on specific conditions
• Combine multiple conditions logically with AND, OR, and parentheses
• Efficiently filter ranges with BETWEEN and lists with IN
• Sort results meaningfully with ORDER BY
• Handle text patterns with LIKE and wildcards

Practice recommendations:

1. Start with simple, single-condition WHERE clauses and gradually add complexity
2. Always test complex logic with a small dataset first
3. Use parentheses liberally when mixing AND/OR to avoid confusion
4. Remember that ORDER BY goes at the very end of your query

Next steps in your SQL journey:

• Learn about aggregate functions (COUNT, SUM, AVG) to summarize your filtered data
• Explore grouping data with GROUP BY to create category-based summaries
• Master joins to combine data from multiple tables
• Dive into subqueries for more sophisticated filtering logic

The filtering and sorting techniques you've learned here will be used in virtually every advanced SQL concept you encounter. Master these fundamentals, and you'll have a solid foundation for tackling any data challenge that comes your way.