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

Imagine you're a sales analyst at an e-commerce company, and your manager asks you a simple question: "How did our electronics products perform last quarter, and which ones sold the best?" You have a database with thousands of product records spanning multiple years and categories. Without the right tools to filter and sort this data, you'd be drowning in irrelevant information.

This is where SQL's filtering and sorting capabilities become your lifeline. By the end of this lesson, you'll know exactly how to slice through massive datasets to find precisely the information you need, and present it in the most useful order. You'll transform from someone who gets overwhelmed by data to someone who confidently extracts insights from it.

What you'll learn:

• How to filter records using WHERE clauses with specific conditions
• How to combine multiple conditions using AND, OR, and parentheses for complex logic
• How to efficiently filter for multiple values using IN and ranges using BETWEEN
• How to sort your results in ascending or descending order with ORDER BY
• How to combine filtering and sorting to create powerful, focused queries

Prerequisites

This lesson assumes you understand basic SQL SELECT statements and can connect to a database. If you're new to SQL, complete the "Basic SELECT Statements" lesson first.

Understanding the WHERE Clause: Your Data Filter

Think of the WHERE clause like a security checkpoint at an airport. Every row in your table must pass through this checkpoint, and only the rows that meet your specified conditions get through to your results.

The basic structure looks like this:

SELECT column1, column2, column3
FROM table_name
WHERE condition;

Let's work with a realistic example. Imagine we have a products table for our e-commerce store:

-- Sample data in our products table
product_id | product_name          | category    | price | stock_quantity | supplier_id
1          | iPhone 14 Pro         | Electronics | 999   | 45            | 101
2          | Samsung Galaxy S23    | Electronics | 899   | 32            | 102
3          | Coffee Maker Pro      | Home        | 129   | 78            | 103
4          | Running Shoes Nike    | Sports      | 120   | 156           | 104
5          | Laptop Dell XPS       | Electronics | 1299  | 23            | 101
6          | Yoga Mat Premium      | Sports      | 45    | 89            | 105

Let's start with a simple filter to find all electronics:

SELECT product_name, price, stock_quantity
FROM products
WHERE category = 'Electronics';

This returns:

product_name          | price | stock_quantity
iPhone 14 Pro         | 999   | 45
Samsung Galaxy S23    | 899   | 32
Laptop Dell XPS       | 1299  | 23

Notice how the WHERE clause acts as a gatekeeper, only allowing rows where the category column exactly matches 'Electronics'.

Comparison Operators: Beyond Simple Equality

Equality (=) is just one way to filter data. SQL provides several comparison operators that let you create more sophisticated conditions:

-- Products more expensive than $500
SELECT product_name, price
FROM products
WHERE price > 500;

-- Products with low stock (25 or fewer items)
SELECT product_name, stock_quantity
FROM products
WHERE stock_quantity <= 25;

-- Products that are NOT in the Electronics category
SELECT product_name, category
FROM products
WHERE category != 'Electronics';
-- or use: WHERE category <> 'Electronics';

Tip: Use != or <> for "not equal" – both work in most databases, but <> is the SQL standard.

Combining Conditions with AND: Meeting Multiple Requirements

Real-world questions often require multiple conditions. The AND operator ensures that ALL conditions must be true for a row to be included in your results.

-- Find expensive electronics (Electronics AND price > 800)
SELECT product_name, category, price
FROM products
WHERE category = 'Electronics' 
  AND price > 800;

This returns only products that are BOTH in the Electronics category AND cost more than $800:

product_name          | category    | price
iPhone 14 Pro         | Electronics | 999
Samsung Galaxy S23    | Electronics | 899
Laptop Dell XPS       | Electronics | 1299

You can chain multiple AND conditions:

-- Electronics that are expensive AND have low stock
SELECT product_name, price, stock_quantity
FROM products
WHERE category = 'Electronics'
  AND price > 800
  AND stock_quantity < 50;

This narrows it down to electronics that meet ALL three conditions.

Using OR for Alternative Conditions

While AND requires all conditions to be true, OR requires only ONE condition to be true. This is perfect when you want items that meet any of several criteria.

-- Products that are either very expensive OR have very low stock
SELECT product_name, price, stock_quantity
FROM products
WHERE price > 1000
   OR stock_quantity < 30;

This might return:

product_name          | price | stock_quantity
Samsung Galaxy S23    | 899   | 32              -- Low stock
Laptop Dell XPS       | 1299  | 23              -- Both expensive AND low stock

Notice how the Samsung Galaxy S23 appears because it has low stock (32 < 30 is false, but we also check price > 1000 which is false, but stock < 30... wait, let me recalculate that properly):

product_name          | price | stock_quantity
Laptop Dell XPS       | 1299  | 23              -- Expensive (>1000)

Actually, only the Laptop Dell XPS meets our criteria (price > 1000 OR stock < 30).

Mastering Complex Logic with Parentheses

When you combine AND and OR, parentheses become crucial for controlling the order of operations. Without them, you might get unexpected results.

Consider this business question: "Find products that are either (expensive Electronics) or (any Sports items with low stock)."

Wrong way (without parentheses):

-- This doesn't work as expected
SELECT product_name, category, price, stock_quantity
FROM products
WHERE category = 'Electronics' 
  AND price > 500
  OR category = 'Sports'
  AND stock_quantity < 50;

This is interpreted as: (Electronics AND expensive) OR (Sports AND low stock), which might not be what you want.

Right way (with parentheses):

-- This clearly expresses the business logic
SELECT product_name, category, price, stock_quantity
FROM products
WHERE (category = 'Electronics' AND price > 500)
   OR (category = 'Sports' AND stock_quantity < 50);

The parentheses make your intention crystal clear and ensure the logic works exactly as you intended.

Efficient Filtering with IN: Multiple Values Made Easy

When you need to check if a column matches any value from a list, the IN operator is much cleaner than multiple OR conditions.

Clunky way:

-- Finding products from specific suppliers (the hard way)
SELECT product_name, supplier_id
FROM products
WHERE supplier_id = 101
   OR supplier_id = 103
   OR supplier_id = 105;

Clean way:

-- Finding products from specific suppliers (the elegant way)
SELECT product_name, supplier_id
FROM products
WHERE supplier_id IN (101, 103, 105);

Both queries return the same results, but the IN version is much more readable and maintainable. You can also use IN with text values:

-- Products in multiple categories
SELECT product_name, category, price
FROM products
WHERE category IN ('Electronics', 'Sports');

The opposite of IN is NOT IN:

-- Everything except Electronics and Sports
SELECT product_name, category
FROM products
WHERE category NOT IN ('Electronics', 'Sports');

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. Use WHERE column NOT IN (value1, value2) OR column IS NULL if you need to handle NULLs.

Range Filtering with BETWEEN: Elegant Boundary Conditions

When you need to find values within a range, BETWEEN provides a clean alternative to using AND with comparison operators.

Verbose way:

-- Products in a price range (the long way)
SELECT product_name, price
FROM products
WHERE price >= 100 AND price <= 500;

Concise way:

-- Products in a price range (the elegant way)
SELECT product_name, price
FROM products
WHERE price BETWEEN 100 AND 500;

BETWEEN is inclusive of both endpoints, meaning it includes products that cost exactly $100 and exactly $500.

BETWEEN works with dates too, which is incredibly useful for time-based analysis:

-- If we had a date column, we could do:
-- SELECT product_name, launch_date
-- FROM products
-- WHERE launch_date BETWEEN '2023-01-01' AND '2023-12-31';

You can also use NOT BETWEEN:

-- Products that are either very cheap or very expensive
SELECT product_name, price
FROM products
WHERE price NOT BETWEEN 100 AND 500;

Sorting Results with ORDER BY: Making Data Meaningful

Finding the right data is only half the battle. The ORDER BY clause sorts your results to make them meaningful and actionable.

-- Products sorted by price (lowest to highest)
SELECT product_name, price
FROM products
ORDER BY price;

By default, ORDER BY sorts in ascending order (ASC). For descending order, use DESC:

-- Products sorted by price (highest to lowest)
SELECT product_name, price
FROM products
ORDER BY price DESC;

You can sort by multiple columns. The database sorts by the first column, then breaks ties using the second column:

-- Sort by category first, then by price within each category
SELECT product_name, category, price
FROM products
ORDER BY category, price DESC;

This gives you products grouped by category, with the most expensive items first within each category.

You can mix ascending and descending:

-- Categories alphabetically, but prices highest to lowest within each category
SELECT product_name, category, price
FROM products
ORDER BY category ASC, price DESC;

Combining WHERE and ORDER BY: The Complete Picture

The real power emerges when you combine filtering with sorting. The ORDER BY clause always comes after WHERE:

-- Electronics under $1000, sorted by price
SELECT product_name, price, stock_quantity
FROM products
WHERE category = 'Electronics'
  AND price < 1000
ORDER BY price DESC;

This gives you a clear view of your affordable electronics options, with the most expensive ones first.

Here's a more complex example combining everything we've learned:

-- Premium products from preferred suppliers, sorted strategically
SELECT product_name, category, price, stock_quantity, supplier_id
FROM products
WHERE (price > 200 OR category = 'Electronics')
  AND supplier_id IN (101, 102, 103)
  AND stock_quantity BETWEEN 20 AND 100
ORDER BY category, price DESC;

This query finds products that are either expensive (over $200) OR electronics, from our preferred suppliers (101, 102, 103), with reasonable stock levels (20-100 units), sorted by category then price.

Text Pattern Matching with LIKE

While not in our main topic list, LIKE is essential for text filtering and works beautifully with our other operators:

-- Products with "Pro" in the name, sorted by price
SELECT product_name, price
FROM products
WHERE product_name LIKE '%Pro%'
ORDER BY price;

The % wildcard matches any sequence of characters. You can also use _ to match exactly one character.

Hands-On Exercise

Let's put everything together with a comprehensive exercise. Imagine you're preparing a report for your manager about product inventory.

Scenario: Create a query that finds products meeting these business requirements:

1. Either in 'Electronics' or 'Sports' categories
2. Either have low stock (under 40 units) OR are expensive (over $200)
3. NOT from supplier 104
4. Sort by category, then by stock quantity (lowest first)

Try writing this query yourself before looking at the solution:

SELECT product_name, category, price, stock_quantity, supplier_id
FROM products
WHERE category IN ('Electronics', 'Sports')
  AND (stock_quantity < 40 OR price > 200)
  AND supplier_id != 104
ORDER BY category, stock_quantity;

Now run your query and verify it returns products that meet all the criteria.

Common Mistakes & Troubleshooting

1. Forgetting quotes around text values

-- Wrong - will cause an error
WHERE category = Electronics

-- Right - text values need quotes
WHERE category = 'Electronics'

2. Incorrect NULL handling

-- Wrong - this will never find NULL values
WHERE some_column = NULL

-- Right - use IS NULL for NULL values
WHERE some_column IS NULL

3. Mixing up AND/OR logic

-- This finds expensive items OR items in Electronics (regardless of price)
WHERE price > 1000 OR category = 'Electronics'

-- This finds expensive Electronics only
WHERE price > 1000 AND category = 'Electronics'

4. Case sensitivity issues In some databases, text comparisons are case-sensitive:

-- Might not match 'electronics' or 'ELECTRONICS'
WHERE category = 'Electronics'

-- Use UPPER() or LOWER() for case-insensitive matching
WHERE UPPER(category) = 'ELECTRONICS'

5. Performance with OR conditions If you have multiple OR conditions on the same column, consider using IN instead:

-- Slower
WHERE supplier_id = 101 OR supplier_id = 102 OR supplier_id = 103

-- Faster
WHERE supplier_id IN (101, 102, 103)

Summary & Next Steps

You've now mastered the fundamental tools for filtering and sorting data in SQL. You can:

• Use WHERE clauses to filter records based on specific conditions
• Combine conditions logically using AND, OR, and parentheses
• Efficiently filter for multiple values with IN and ranges with BETWEEN
• Sort results meaningfully using ORDER BY with multiple columns
• Combine all these techniques to answer complex business questions

These skills form the foundation of data analysis with SQL. Every time you need to answer a question like "Which customers bought what products when?" or "What are our top-performing items by region?", you'll use these filtering and sorting techniques.

Practice suggestions:

1. Start with simple WHERE conditions and gradually add complexity
2. Always test your logic with small datasets first
3. Use parentheses liberally when combining AND/OR – clarity is more important than brevity
4. Practice with real datasets from your work or publicly available data

Coming up next: Learn how to group data and calculate aggregates (COUNT, SUM, AVG) to turn your filtered data into meaningful insights and reports.