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

Imagine you're analyzing sales data for a company with 50,000 customer transactions. Your boss asks three questions: "Which customers spent more than $500 last month?", "Show me all orders from California or Texas," and "Can you sort these by date, newest first?" Without the ability to filter and sort your data, you'd be scrolling through endless spreadsheet rows or database records like looking for a needle in a haystack.

This is where SQL's filtering and sorting capabilities become indispensable. Every data professional needs to master these fundamental operations because raw data is rarely in the exact format you need. Whether you're a business analyst preparing a monthly report, a marketing specialist segmenting customers, or a data scientist cleaning datasets for machine learning, you'll use these techniques daily.

By the end of this lesson, you'll confidently slice through massive datasets to find exactly what you need, arrange data in meaningful ways, and combine multiple conditions to answer complex business questions.

What you'll learn:

• Filter data using WHERE clauses with comparison and logical operators
• Combine multiple conditions with AND, OR, and NOT operators
• Use IN and BETWEEN for efficient range-based filtering
• Sort results with ORDER BY for single and multiple columns
• Apply best practices for writing readable, maintainable query conditions

Prerequisites

This lesson assumes you understand basic SQL SELECT statements and can connect to a database. You should be comfortable with the concept of tables, rows, and columns, and know how to write simple queries like SELECT * FROM table_name.

Understanding the WHERE Clause: Your Data Filter

The WHERE clause is like a security checkpoint for your data—it only lets through rows that meet your specific criteria. Think of it as asking your database: "Show me only the records where this condition is true."

Let's work with a realistic customer orders dataset to explore filtering. Here's the structure we'll use:

-- Sample orders table structure
CREATE TABLE orders (
    order_id INT,
    customer_name VARCHAR(100),
    product_category VARCHAR(50),
    order_amount DECIMAL(10,2),
    order_date DATE,
    customer_state VARCHAR(2),
    shipping_method VARCHAR(20)
);

The basic syntax for filtering follows this pattern:

SELECT column_names
FROM table_name
WHERE condition;

Let's start with simple comparisons. Say you want to find all orders worth more than $1,000:

SELECT order_id, customer_name, order_amount
FROM orders
WHERE order_amount > 1000;

This query examines each row in the orders table and only returns those where the order_amount column contains a value greater than 1,000. The comparison operators you can use include:

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

Here's how you might filter for exact matches:

-- Find all orders from California
SELECT *
FROM orders
WHERE customer_state = 'CA';

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

For date filtering, you'll often want to find records within specific time periods:

-- Orders placed on or after January 1, 2024
SELECT order_id, customer_name, order_date
FROM orders
WHERE order_date >= '2024-01-01';

Combining Conditions with AND, OR, and NOT

Real-world questions rarely involve just one condition. You'll often need to combine multiple criteria, which is where logical operators become powerful.

The AND Operator: All Conditions Must Be True

AND works like a strict bouncer—every condition must be satisfied for a row to pass through. Let's find high-value orders from specific states:

-- Orders over $500 AND from California
SELECT order_id, customer_name, order_amount, customer_state
FROM orders
WHERE order_amount > 500 
  AND customer_state = 'CA';

You can chain multiple AND conditions together:

-- Premium orders: high value, recent, and expedited shipping
SELECT *
FROM orders
WHERE order_amount > 1000
  AND order_date >= '2024-01-01'
  AND shipping_method = 'Express';

The OR Operator: Any Condition Can Be True

OR is more flexible—if any of the conditions is true, the row makes it through. This is perfect for including multiple categories or ranges:

-- Orders from California OR Texas
SELECT order_id, customer_name, customer_state
FROM orders
WHERE customer_state = 'CA' 
   OR customer_state = 'TX';

You can mix different types of conditions with OR:

-- Either high-value orders OR recent orders
SELECT order_id, customer_name, order_amount, order_date
FROM orders
WHERE order_amount > 2000 
   OR order_date >= '2024-03-01';

The NOT Operator: Excluding Results

NOT flips the logic—it returns rows where the condition is false. This is useful for exclusion-based filtering:

-- All orders except those from California
SELECT order_id, customer_name, customer_state
FROM orders
WHERE NOT customer_state = 'CA';

-- Alternative syntax
SELECT order_id, customer_name, customer_state
FROM orders
WHERE customer_state != 'CA';

Combining AND and OR: Order of Operations Matters

When you mix AND and OR, SQL evaluates AND first, just like multiplication before addition in math. Use parentheses to control the logic:

-- Without parentheses - might not be what you want
SELECT *
FROM orders
WHERE order_amount > 1000 
   OR customer_state = 'CA' 
  AND shipping_method = 'Express';

-- With parentheses - clearer intent
SELECT *
FROM orders
WHERE order_amount > 1000 
   OR (customer_state = 'CA' AND shipping_method = 'Express');

The first query finds orders over $1,000 OR California express orders. The second finds orders over $1,000 OR orders that are both from California AND express shipped.

The IN Operator: Elegant Multiple Value Matching

When you need to match against several specific values, the IN operator is much cleaner than chaining multiple OR conditions.

Instead of writing this cumbersome query:

-- The hard way
SELECT *
FROM orders
WHERE customer_state = 'CA' 
   OR customer_state = 'TX' 
   OR customer_state = 'NY' 
   OR customer_state = 'FL';

You can write this elegant version:

-- The elegant way
SELECT *
FROM orders
WHERE customer_state IN ('CA', 'TX', 'NY', 'FL');

IN works with any data type. Here's how to filter for specific product categories:

SELECT order_id, customer_name, product_category
FROM orders
WHERE product_category IN ('Electronics', 'Books', 'Home & Garden');

You can also use NOT IN to exclude specific values:

-- All orders except from these states
SELECT *
FROM orders
WHERE customer_state NOT IN ('CA', 'TX', 'NY');

Warning: Be careful with NOT IN when your list might contain NULL values. If any value in the IN list is NULL, NOT IN will return no results at all—a common source of confusion.

The BETWEEN Operator: Range Filtering Made Simple

BETWEEN is perfect for finding values within a range. It's inclusive, meaning it includes the boundary values you specify.

For numerical ranges:

-- Orders between $500 and $2000 (inclusive)
SELECT order_id, customer_name, order_amount
FROM orders
WHERE order_amount BETWEEN 500 AND 2000;

This is equivalent to:

SELECT order_id, customer_name, order_amount
FROM orders
WHERE order_amount >= 500 AND order_amount <= 2000;

BETWEEN works beautifully with dates:

-- Orders from the first quarter of 2024
SELECT order_id, customer_name, order_date
FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-03-31';

You can also use NOT BETWEEN for exclusions:

-- Orders outside the typical range
SELECT order_id, customer_name, order_amount
FROM orders
WHERE order_amount NOT BETWEEN 100 AND 1000;

Sorting Results with ORDER BY

Raw query results come back in whatever order the database feels like returning them—usually not helpful for analysis. ORDER BY lets you control the sequence, making your data meaningful and readable.

Basic Sorting

The basic syntax is straightforward:

SELECT column_names
FROM table_name
WHERE conditions
ORDER BY column_name;

By default, ORDER BY sorts in ascending order (smallest to largest, A to Z):

-- Orders sorted by amount, lowest first
SELECT order_id, customer_name, order_amount
FROM orders
ORDER BY order_amount;

For descending order (largest to smallest, Z to A), add DESC:

-- Orders sorted by amount, highest first
SELECT order_id, customer_name, order_amount
FROM orders
ORDER BY order_amount DESC;

Sorting by Multiple Columns

You can sort by multiple columns to create more sophisticated ordering. SQL sorts by the first column, then breaks ties using the second column, and so on:

-- Sort by state, then by amount within each state
SELECT customer_name, customer_state, order_amount
FROM orders
ORDER BY customer_state, order_amount DESC;

This query first groups results by state alphabetically, then within each state, shows the highest amounts first. You can mix ASC and DESC:

-- Sort by state (A-Z), then by date (newest first)
SELECT customer_name, customer_state, order_date
FROM orders
ORDER BY customer_state ASC, order_date DESC;

Sorting by Column Position

You can reference columns by their position number in the SELECT clause—useful for complex expressions:

SELECT customer_name, order_amount, order_date
FROM orders
ORDER BY 2 DESC, 3;  -- Sort by column 2 (amount) desc, then column 3 (date) asc

Best Practice: While sorting by position works, using actual column names makes your queries more readable and maintainable.

Combining Filtering and Sorting

The real power emerges when you combine WHERE clauses with ORDER BY to create precisely targeted, well-organized results:

-- High-value recent orders from key states, sorted by amount
SELECT order_id, customer_name, customer_state, order_amount, order_date
FROM orders
WHERE order_amount > 1000
  AND order_date >= '2024-01-01'
  AND customer_state IN ('CA', 'TX', 'NY', 'FL')
ORDER BY order_amount DESC, order_date DESC;

This query demonstrates a complete filtering and sorting workflow:

1. Filters for orders over $1,000
2. Includes only orders from 2024 onwards
3. Limits to four key states
4. Shows highest amounts first, with most recent dates breaking ties

Hands-On Exercise

Let's practice with a realistic scenario. You're analyzing an e-commerce company's order data and need to answer several business questions. Use this sample data structure and try writing queries for each requirement:

-- Sample data to work with
CREATE TABLE orders (
    order_id INT,
    customer_name VARCHAR(100),
    product_category VARCHAR(50),
    order_amount DECIMAL(10,2),
    order_date DATE,
    customer_state VARCHAR(2),
    shipping_method VARCHAR(20)
);

Exercise 1: Basic Filtering Write queries to find:

1. All orders with amounts between $250 and $750
2. Orders from customers in Washington, Oregon, or California
3. All orders except those in the 'Books' category

Exercise 2: Complex Conditions Write queries to find:

1. High-value orders (>$1000) from the last quarter of 2023 OR any orders over $2500
2. Express shipped orders under $500 from Texas or Florida
3. Electronics or Home & Garden orders over $300, excluding California

Exercise 3: Sorting Challenges Write queries to:

1. Show all orders sorted by state, then by amount (highest first) within each state
2. Display the 20 most recent orders with their details
3. List orders by category, showing the most expensive in each category first

Here are sample solutions:

-- Exercise 1.1
SELECT * FROM orders 
WHERE order_amount BETWEEN 250 AND 750;

-- Exercise 1.2
SELECT * FROM orders 
WHERE customer_state IN ('WA', 'OR', 'CA');

-- Exercise 1.3
SELECT * FROM orders 
WHERE product_category != 'Books';

-- Exercise 2.1
SELECT * FROM orders 
WHERE (order_amount > 1000 AND order_date >= '2023-10-01') 
   OR order_amount > 2500;

-- Exercise 2.2
SELECT * FROM orders 
WHERE shipping_method = 'Express' 
  AND order_amount < 500 
  AND customer_state IN ('TX', 'FL');

-- Exercise 3.1
SELECT * FROM orders 
ORDER BY customer_state, order_amount DESC;

-- Exercise 3.2
SELECT * FROM orders 
ORDER BY order_date DESC 
LIMIT 20;

Common Mistakes & Troubleshooting

Mistake 1: Forgetting Quotes Around Text Values

-- Wrong - will cause an error
SELECT * FROM orders WHERE customer_state = CA;

-- Right - text values need single quotes
SELECT * FROM orders WHERE customer_state = 'CA';

Mistake 2: Misunderstanding AND vs OR Logic

-- This finds orders over $1000 OR from CA (includes all CA orders regardless of amount)
SELECT * FROM orders 
WHERE order_amount > 1000 OR customer_state = 'CA';

-- This finds orders over $1000 AND from CA (only high-value CA orders)
SELECT * FROM orders 
WHERE order_amount > 1000 AND customer_state = 'CA';

Mistake 3: Date Format Issues

Different databases expect dates in different formats. Most accept 'YYYY-MM-DD', but some might require specific functions:

-- Usually works
WHERE order_date = '2024-01-15'

-- Sometimes needed (SQL Server)
WHERE order_date = '2024-01-15'

-- For time zones (PostgreSQL)
WHERE order_date = '2024-01-15'::date

Mistake 4: NULL Value Surprises

NULL values can cause unexpected results with comparisons:

-- This won't find rows where order_amount is NULL
SELECT * FROM orders WHERE order_amount > 100;

-- To include NULLs in your logic
SELECT * FROM orders 
WHERE order_amount > 100 OR order_amount IS NULL;

Mistake 5: Inefficient NOT IN with Large Lists

-- Can be slow with large lists
WHERE customer_state NOT IN ('CA', 'TX', 'NY', 'FL', ...);

-- Often faster alternative
WHERE customer_state NOT EXISTS (
    SELECT 1 FROM excluded_states 
    WHERE state = customer_state
);

Summary & Next Steps

You've now mastered the fundamental tools for filtering and sorting data in SQL. These skills form the foundation of virtually every data analysis task you'll encounter. You can:

• Use WHERE clauses with comparison operators to filter rows
• Combine multiple conditions with AND, OR, and NOT
• Efficiently match multiple values using IN and ranges with BETWEEN
• Sort results meaningfully with ORDER BY for single and multiple columns
• Troubleshoot common filtering and sorting issues

The techniques you've learned here will be used in nearly every SQL query you write. Whether you're preparing data for visualization, generating business reports, or feeding clean datasets into analytics tools, filtering and sorting are your first line of data refinement.

Next recommended topics:

• Aggregate Functions and GROUP BY: Learn to summarize data with COUNT, SUM, AVG, and grouping
• Working with NULL Values: Master IS NULL, IS NOT NULL, and COALESCE
• String Functions and Pattern Matching: Use LIKE, wildcards, and text manipulation functions
• Joins: Combine data from multiple tables to answer complex business questions

Practice these filtering and sorting techniques regularly with your own datasets. The more you use them, the more intuitive they'll become, and you'll start seeing opportunities to apply them in increasingly sophisticated ways.