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

You're staring at a spreadsheet with 50,000 customer records. Your manager needs a list of all premium customers from California and Texas who joined in the last two years, sorted by their subscription value. In Excel, this would take dozens of clicks, multiple filter dropdowns, and probably some creative scrolling. In SQL, it's one elegant query that takes seconds to write and milliseconds to run.

This is the power of SQL's filtering and sorting capabilities. While spreadsheets force you to manually hunt through data, SQL lets you describe exactly what you want, and the database finds it for you. Think of it as having a hyper-intelligent assistant who can instantly locate any piece of information in a massive filing cabinet.

What you'll learn:

• Use WHERE clauses to filter data based on specific conditions
• Combine multiple criteria with AND, OR, and NOT operators
• Filter efficiently using IN for multiple values and BETWEEN for ranges
• Sort results with ORDER BY to present data in meaningful sequences
• Troubleshoot common filtering and sorting mistakes that trip up beginners

Prerequisites

You should be comfortable with basic SELECT statements and understand how relational databases store data in tables with rows and columns. We'll be working with standard SQL syntax that works across PostgreSQL, MySQL, SQL Server, and other major database systems.

Understanding the WHERE Clause: Your Data Filter

The WHERE clause is SQL's primary filtering mechanism. It acts like a bouncer at a club—only rows that meet your specified conditions get through to the final result set.

Let's work with a realistic customer database. Imagine you're analyzing data for a SaaS company with this customer table structure:

-- Sample customers table
CREATE TABLE customers (
    customer_id INT,
    name VARCHAR(100),
    email VARCHAR(100),
    state VARCHAR(2),
    subscription_tier VARCHAR(20),
    monthly_revenue DECIMAL(10,2),
    signup_date DATE,
    is_active BOOLEAN
);

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 name, email, state 
FROM customers 
WHERE state = 'CA';

This query scans every row in the customers table and only returns those where the state column exactly equals 'CA'. Notice that we use single quotes around 'CA' because it's a text value—this is crucial for string comparisons.

For numeric comparisons, you don't need quotes:

SELECT name, monthly_revenue 
FROM customers 
WHERE monthly_revenue > 100;

This finds all customers generating more than $100 in monthly revenue. SQL supports all the comparison operators you'd expect:

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

Important: Be careful with NULL values. If a customer has NULL in their monthly_revenue field, the condition monthly_revenue > 100 will NOT include that row, even if you might logically expect it to. We'll cover handling NULLs later in this series.

Combining Conditions with AND and OR

Real-world queries rarely filter on just one condition. You'll typically need to combine multiple criteria to get exactly the data you want. This is where AND and OR operators become essential.

Using AND for Multiple Requirements

The AND operator requires ALL conditions to be true for a row to be included. Think of it as being picky—every single requirement must be met.

SELECT name, state, subscription_tier, monthly_revenue
FROM customers 
WHERE state = 'CA' 
  AND subscription_tier = 'Premium' 
  AND monthly_revenue > 200;

This query finds customers who are:

1. Located in California, AND
2. Have a Premium subscription, AND
3. Generate more than $200 monthly revenue

All three conditions must be true for a customer to appear in the results. If a customer is in California with a Premium subscription but only generates $150 monthly, they won't be included.

Using OR for Alternative Options

The OR operator is more inclusive—it returns rows where ANY of the conditions are true. It's like saying "I'll accept this OR that OR the other thing."

SELECT name, state, subscription_tier
FROM customers 
WHERE state = 'CA' 
   OR state = 'TX' 
   OR state = 'NY';

This finds customers from California, Texas, OR New York. A customer only needs to be in one of these states to be included.

Mixing AND with OR: Parentheses Matter

Here's where things get interesting—and where many beginners make mistakes. When you combine AND and OR in the same query, you must use parentheses to make your logic crystal clear.

Consider this query without parentheses:

-- PROBLEMATIC: Logic is ambiguous
SELECT name, state, subscription_tier, monthly_revenue
FROM customers 
WHERE state = 'CA' 
   OR state = 'TX' 
  AND subscription_tier = 'Premium';

Does this find:

1. Premium customers from Texas, plus ALL customers from California?
2. Premium customers from either California or Texas?

The answer is #1, because AND has higher precedence than OR. But that's probably not what you intended! Use parentheses to make your logic explicit:

-- CLEAR: Premium customers from CA or TX
SELECT name, state, subscription_tier, monthly_revenue
FROM customers 
WHERE (state = 'CA' OR state = 'TX') 
  AND subscription_tier = 'Premium';

-- ALSO CLEAR: All CA customers, plus Premium TX customers
SELECT name, state, subscription_tier, monthly_revenue
FROM customers 
WHERE state = 'CA' 
   OR (state = 'TX' AND subscription_tier = 'Premium');

Both queries are valid, but they return completely different results. Always use parentheses when combining AND and OR—your future self will thank you.

Efficient Filtering with IN and BETWEEN

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

The IN Operator: Multiple Values Made Easy

Instead of writing multiple OR conditions for the same column, use the IN operator:

-- Instead of this verbose approach:
SELECT name, state 
FROM customers 
WHERE state = 'CA' OR state = 'TX' OR state = 'NY' OR state = 'FL';

-- Use this cleaner syntax:
SELECT name, state 
FROM customers 
WHERE state IN ('CA', 'TX', 'NY', 'FL');

The IN operator is particularly useful when you have a long list of values. It's more readable and often performs better than equivalent OR chains.

You can also use IN with numeric values:

SELECT name, customer_id 
FROM customers 
WHERE customer_id IN (1001, 1045, 2033, 2156, 3421);

The opposite of IN is NOT IN, which excludes rows matching any of the listed values:

SELECT name, state 
FROM customers 
WHERE state NOT IN ('AK', 'HI');  -- Exclude Alaska and Hawaii

The BETWEEN Operator: Range Filtering

When you need to filter on a range of values, BETWEEN is more readable than using >= and <= operators:

-- Instead of:
SELECT name, monthly_revenue 
FROM customers 
WHERE monthly_revenue >= 100 AND monthly_revenue <= 500;

-- Use:
SELECT name, monthly_revenue 
FROM customers 
WHERE monthly_revenue BETWEEN 100 AND 500;

BETWEEN is inclusive on both ends—it includes rows where the value equals the lower bound, upper bound, or anything in between.

BETWEEN works great with dates too:

SELECT name, signup_date 
FROM customers 
WHERE signup_date BETWEEN '2023-01-01' AND '2023-12-31';

This finds all customers who signed up during 2023.

You can also use NOT BETWEEN to exclude ranges:

SELECT name, monthly_revenue 
FROM customers 
WHERE monthly_revenue NOT BETWEEN 50 AND 150;

This finds customers with monthly revenue either below $50 or above $150.

Sorting Results with ORDER BY

Filtering gets you the right data, but sorting presents it in a meaningful order. The ORDER BY clause always goes at the end of your query and determines how rows are arranged in the final result.

Basic Sorting

The simplest ORDER BY sorts by one column in ascending order (lowest to highest, A to Z):

SELECT name, monthly_revenue 
FROM customers 
WHERE state = 'CA'
ORDER BY monthly_revenue;

This returns California customers sorted from lowest to highest monthly revenue.

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

SELECT name, monthly_revenue 
FROM customers 
WHERE state = 'CA'
ORDER BY monthly_revenue DESC;

Multi-Level Sorting

You can sort by multiple columns to create sophisticated ordering. The first column is the primary sort, the second column breaks ties in the first, and so on:

SELECT name, state, subscription_tier, monthly_revenue 
FROM customers 
WHERE state IN ('CA', 'TX', 'NY')
ORDER BY state, subscription_tier DESC, monthly_revenue DESC;

This query sorts results by:

1. State (alphabetically: CA, NY, TX)
2. Within each state, by subscription tier (descending: Premium, Standard, Basic)
3. Within each state/tier combination, by monthly revenue (highest first)

So you might see results like:

• CA Premium customers (sorted by revenue, highest first)
• CA Standard customers (sorted by revenue, highest first)
• CA Basic customers (sorted by revenue, highest first)
• NY Premium customers (sorted by revenue, highest first)
• And so on...

Sorting by Column Position

Instead of column names, you can reference columns by their position in the SELECT list:

SELECT name, state, monthly_revenue 
FROM customers 
ORDER BY 2, 3 DESC;  -- Sort by state (column 2), then revenue descending (column 3)

While this works, it's generally better to use column names for clarity. Column position sorting becomes fragile if you ever modify the SELECT list.

Pattern Matching with LIKE

Sometimes you need to filter on partial text matches rather than exact values. The LIKE operator lets you search for patterns using wildcards:

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

-- Find customers whose names start with 'Smith'
SELECT name, email 
FROM customers 
WHERE name LIKE 'Smith%';

-- Find customers with Gmail addresses
SELECT name, email 
FROM customers 
WHERE email LIKE '%@gmail.com';

-- Find customers with exactly 4-character names
SELECT name 
FROM customers 
WHERE name LIKE '____';  -- Four underscores

You can combine LIKE with other conditions:

SELECT name, email, state 
FROM customers 
WHERE state IN ('CA', 'TX') 
  AND email LIKE '%@gmail.com'
ORDER BY name;

Performance tip: LIKE patterns that start with a wildcard (like LIKE '%something') can be slow on large datasets because the database can't use indexes efficiently. When possible, put wildcards at the end (LIKE 'something%').

Hands-On Exercise

Let's put everything together with a comprehensive exercise. Imagine you're a data analyst for an e-commerce company, and your marketing team needs a targeted customer list for a new campaign.

Create queries to find:

1. High-value recent customers: Customers from California, Texas, or New York who signed up in 2023 and have monthly revenue between $150 and $500, sorted by revenue (highest first).

2. Premium customer analysis: All Premium or Enterprise customers from any state except Alaska and Hawaii, sorted by state, then by signup date (newest first).

3. Email campaign targeting: Customers with Gmail or Yahoo email addresses who are currently active and have monthly revenue above $200, excluding Basic tier customers.

Here are the solutions:

-- Query 1: High-value recent customers
SELECT name, email, state, monthly_revenue, signup_date
FROM customers 
WHERE state IN ('CA', 'TX', 'NY') 
  AND signup_date BETWEEN '2023-01-01' AND '2023-12-31'
  AND monthly_revenue BETWEEN 150 AND 500
ORDER BY monthly_revenue DESC;

-- Query 2: Premium customer analysis
SELECT name, state, subscription_tier, signup_date
FROM customers 
WHERE subscription_tier IN ('Premium', 'Enterprise')
  AND state NOT IN ('AK', 'HI')
ORDER BY state, signup_date DESC;

-- Query 3: Email campaign targeting
SELECT name, email, monthly_revenue, subscription_tier
FROM customers 
WHERE (email LIKE '%@gmail.com' OR email LIKE '%@yahoo.com')
  AND is_active = true
  AND monthly_revenue > 200
  AND subscription_tier <> 'Basic'
ORDER BY monthly_revenue DESC;

Try writing these queries yourself before looking at the solutions. Notice how each query combines multiple filtering techniques we've covered.

Common Mistakes & Troubleshooting

Mistake #1: Forgetting Quotes Around Text Values

-- WRONG: This will cause an error
SELECT name FROM customers WHERE state = CA;

-- RIGHT: Text values need quotes
SELECT name FROM customers WHERE state = 'CA';

SQL will interpret CA without quotes as a column name, not the literal text "CA".

Mistake #2: Confusing AND/OR Logic

-- This query probably doesn't do what you think
SELECT name FROM customers 
WHERE state = 'CA' OR state = 'TX' AND subscription_tier = 'Premium';

-- Be explicit with parentheses
SELECT name FROM customers 
WHERE (state = 'CA' OR state = 'TX') AND subscription_tier = 'Premium';

Mistake #3: Case Sensitivity Issues

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

-- Might miss customers with lowercase emails
WHERE email LIKE '%@Gmail.com'

-- Better: use UPPER() or LOWER() functions for consistency
WHERE UPPER(email) LIKE '%@GMAIL.COM'

Mistake #4: NULL Value Surprises

-- This won't find customers where monthly_revenue is NULL
WHERE monthly_revenue > 0

-- To include NULLs, you need explicit handling
WHERE monthly_revenue > 0 OR monthly_revenue IS NULL

Mistake #5: Incorrect BETWEEN Usage

-- WRONG: This finds nothing because 500 is not between 100 and 500
WHERE 500 BETWEEN monthly_revenue AND 100

-- RIGHT: Lower bound comes first
WHERE monthly_revenue BETWEEN 100 AND 500

Summary & Next Steps

You now have the fundamental tools for filtering and sorting data in SQL. You can use WHERE clauses to find exactly the rows you need, combine conditions with AND and OR operators, leverage IN and BETWEEN for efficient filtering, and sort results meaningfully with ORDER BY.

These techniques form the foundation for more advanced SQL operations. In practice, you'll use filtering and sorting in almost every query you write—they're the building blocks that make SQL powerful for data analysis.

Key takeaways:

• WHERE filters rows; ORDER BY sorts results
• Always use parentheses when combining AND/OR operators
• IN and BETWEEN make complex filters more readable
• LIKE enables pattern matching with wildcards
• Watch out for NULL values and case sensitivity

Next, you'll learn about aggregate functions (COUNT, SUM, AVG) and grouping data, which will let you analyze patterns across your filtered datasets. You'll also explore joins, which combine data from multiple tables—the real power move that separates SQL from spreadsheet tools.

Practice these filtering and sorting techniques on any dataset you have access to. The more you use these operators, the more intuitive they become. Soon you'll be building complex queries that would take hours in Excel but run in seconds in SQL.