02-filtering-with-where.md

Chapter 02: Filtering with WHERE

Filtering is one of the most powerful features of SQL. It allows you to sift through millions of rows and extract only the records that meet specific criteria. In this chapter, we'll explore comparison operators, logical operators, and pattern matching.

2.1 Basic Equality Filter

The simplest way to filter is using the equals sign (=).

-- Find all orders that have been delivered
SELECT *
FROM orders
WHERE order_status = 'delivered'
LIMIT 10;

2.2 Comparison Operators

SQL supports standard comparison operators:

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

-- Find items where freight is more expensive than the product
SELECT
    order_id,
    price,
    freight_value
FROM order_items
WHERE freight_value > price
LIMIT 10;

2.3 Not Equal: != or <>

Both != and <> represent "not equal to". <> is the ANSI standard and is generally preferred for cross-database compatibility.

SELECT
    order_id,
    order_status
FROM orders
WHERE order_status <> 'delivered'
LIMIT 10;

2.4 Logical Operator: AND

Use AND when all conditions must be true.

-- Find delivered orders from RJ (Rio de Janeiro)
SELECT
    o.order_id,
    o.order_status,
    c.customer_state
FROM orders o, customers c
WHERE o.customer_id = c.customer_id
  AND o.order_status = 'delivered'
  AND c.customer_state = 'RJ'
LIMIT 10;

2.5 Logical Operator: OR

Use OR when at least one condition must be true.

-- Find orders that are either shipped or delivered
SELECT
    order_id,
    order_status
FROM orders
WHERE order_status = 'shipped'
   OR order_status = 'delivered'
LIMIT 10;

2.6 The IN Operator

IN is a cleaner way to write multiple OR conditions for the same column.

SELECT
    order_id,
    order_status
FROM orders
WHERE order_status IN ('shipped', 'delivered', 'invoiced')
LIMIT 10;

You can also use NOT IN to exclude specific values.

2.7 BETWEEN for Ranges

BETWEEN filters values within a specific range (inclusive).

-- Find items priced between 100 and 500 BRL
SELECT
    order_id,
    product_id,
    price
FROM order_items
WHERE price BETWEEN 100 AND 500
LIMIT 10;

2.8 LIKE for Pattern Matching

LIKE allows for flexible searching using wildcards:

• % matches any number of characters.
• _ matches exactly one character.

-- Find cities starting with 'rio'
SELECT DISTINCT customer_city
FROM customers
WHERE customer_city LIKE 'rio%'
LIMIT 10;

2.9 Handling NULL Values

NULL represents missing or unknown data. ⚠️ Crucial: You cannot use = with NULL. You must use IS NULL or IS NOT NULL.

-- Find orders that have NOT been delivered yet
SELECT
    order_id,
    order_status,
    order_delivered_customer_date
FROM orders
WHERE order_delivered_customer_date IS NULL
LIMIT 10;

---

Exercises

1. Find all orders with status 'canceled'. How many are there?
2. Find all products where product_weight_g > 10000 (heavier than 10kg).
3. Find all payments made via 'boleto' with payment_value > 200.
4. Find all customers from states 'SP', 'RJ', or 'MG'. Count them.
5. Find all reviews with review_score of 1 that also have a review_comment_message (not NULL).
6. Find all seller cities that start with 'sao'.

Solutions

-- Exercise 1
SELECT COUNT(*) FROM orders WHERE order_status = 'canceled';

-- Exercise 2
SELECT product_id, product_weight_g FROM products WHERE product_weight_g > 10000;

-- Exercise 3
SELECT order_id, payment_type, payment_value FROM order_payments 
WHERE payment_type = 'boleto' AND payment_value > 200;

-- Exercise 4
SELECT COUNT(*) FROM customers WHERE customer_state IN ('SP', 'RJ', 'MG');

-- Exercise 5
SELECT review_id, review_score, review_comment_message FROM order_reviews 
WHERE review_score = 1 AND review_comment_message IS NOT NULL AND review_comment_message != '';

-- Exercise 6
SELECT DISTINCT seller_city FROM sellers WHERE seller_city LIKE 'sao%';