Filtering and Sorting Data

The WHERE Clause

The WHERE clause filters which rows are included in the result. Only rows where the condition evaluates to TRUE are returned.

SELECT order_id, customer_id, total_amount
FROM orders
WHERE total_amount > 500;

Comparison Operators

Logical Operators: AND, OR, NOT

Combine multiple conditions:

AND (both conditions must be true)

SELECT * FROM orders
WHERE country = 'UK'
  AND total_amount > 200
  AND status = 'completed';

OR (at least one condition must be true)

SELECT * FROM products
WHERE category = 'Electronics'
   OR category = 'Computers';

NOT (reverses the condition)

SELECT * FROM orders
WHERE NOT status = 'cancelled';
-- equivalent to: WHERE status != 'cancelled'

Combining AND and OR

Use parentheses to control evaluation order (AND has higher precedence than OR):

-- Orders from UK or Germany, with amount over 100
SELECT * FROM orders
WHERE (country = 'UK' OR country = 'Germany')
  AND total_amount > 100;

Without parentheses, AND binds tighter, which could give unexpected results.

IN: Matching Multiple Values

Instead of multiple OR conditions, use IN:

-- Instead of this:
WHERE country = 'UK' OR country = 'France' OR country = 'Germany'

-- Use this:
WHERE country IN ('UK', 'France', 'Germany')

-- NOT IN excludes values:
WHERE status NOT IN ('cancelled', 'refunded')

BETWEEN: Range Filtering

Filter values within a range (inclusive of both endpoints):

-- Amount between 100 and 500 (includes 100 and 500)
WHERE total_amount BETWEEN 100 AND 500

-- Date range (inclusive)
WHERE order_date BETWEEN '2024-01-01' AND '2024-03-31'

LIKE: Pattern Matching

LIKE is used for partial text matching using wildcards:

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

-- Emails ending in @gmail.com
WHERE email LIKE '%@gmail.com'

-- Names starting with 'Jo'
WHERE name LIKE 'Jo%'

-- Names with exactly 5 characters
WHERE name LIKE '_____'

-- Names containing 'son' anywhere
WHERE name LIKE '%son%'

ILIKE (PostgreSQL only) is case-insensitive LIKE:

WHERE name ILIKE '%london%'  -- matches 'London', 'LONDON', 'london'

Filtering on Dates

Date filtering requires the date values to be stored as actual date types (not text):

-- Specific date
WHERE order_date = '2024-06-15'

-- Date range
WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01'

-- Current year (dynamic)
WHERE EXTRACT(YEAR FROM order_date) = 2024

-- Last 30 days (PostgreSQL)
WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'

-- Last 30 days (BigQuery)
WHERE order_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)

HAVING: Filtering on Aggregated Results

WHERE filters individual rows before aggregation. HAVING filters groups after aggregation.

-- Find customers with more than 5 orders
SELECT
  customer_id,
  COUNT(*) AS order_count,
  SUM(total_amount) AS total_spend
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 5
ORDER BY total_spend DESC;

You cannot use WHERE here because the count does not exist until GROUP BY creates the groups. HAVING filters on the aggregate result.

You can use both WHERE and HAVING in the same query:

-- Find customers in the UK with more than 5 completed orders
SELECT
  customer_id,
  COUNT(*) AS order_count
FROM orders
WHERE country = 'UK'
  AND status = 'completed'
GROUP BY customer_id
HAVING COUNT(*) > 5;

WHERE filters rows first (reducing the rows passed to GROUP BY), then HAVING filters the groups.

Practical Filtering Examples

Segment customers by value

SELECT
  customer_id,
  SUM(total_amount) AS lifetime_value,
  CASE
    WHEN SUM(total_amount) >= 5000 THEN 'VIP'
    WHEN SUM(total_amount) >= 1000 THEN 'Regular'
    ELSE 'Low Value'
  END AS segment
FROM orders
GROUP BY customer_id
ORDER BY lifetime_value DESC;

Find products that have never been ordered

SELECT product_id, name
FROM products
WHERE product_id NOT IN (
  SELECT DISTINCT product_id FROM order_items
);

Key Takeaways

• WHERE filters rows before aggregation using comparison operators (=, !=, >, >=, <, <=) and logical operators (AND, OR, NOT).
• IN simplifies multiple OR conditions; BETWEEN filters inclusive ranges; LIKE enables pattern matching with % and _ wildcards.
• HAVING filters on aggregated results after GROUP BY -- use it where you need to filter on COUNT, SUM, AVG, or other aggregates.
• Use parentheses when combining AND and OR conditions to ensure the correct evaluation order.
• Date filtering requires proper date data types; use EXTRACT or date arithmetic for dynamic date ranges.

Practice Exercise

Write queries to answer these analytical questions:

1. Find all orders where the amount is between $100 and $500, placed in 2024, with status 'completed'
2. Find all customers whose email ends in '@gmail.com' or '@yahoo.com'
3. Find the top 5 customers by total spend who have placed more than 3 orders
4. Find all products in the 'Electronics' or 'Computers' categories with a price less than $200
5. Find all countries where the average order value is greater than $300