Lesson 2: Filtering Data with WHERE

In Lesson 1, we retrieved desired columns with SELECT. But all rows were returned, right? With WHERE, you can pick out only the rows that match your conditions.

The WHERE clause includes only the rows that satisfy the condition in the result. Without WHERE, all rows from the table are returned. Using WHERE is essential for extracting meaningful data in practice.

Concept: WHERE filters out only the rows that match the condition. It is like extracting only 127 VIP members from a total of 5,230.

Comparison Operators

-- = : Retrieve only VIP grade customers
SELECT name, grade FROM customers
WHERE grade = 'VIP';

-- <> : Customers who are not VIP (!= has the same meaning)
SELECT name, grade FROM customers
WHERE grade <> 'VIP';

-- > : Products priced over 1 million won
SELECT name, price FROM products
WHERE price > 1000000;

-- >= : Products with 500 or more in stock
SELECT name, stock_qty FROM products
WHERE stock_qty >= 500;

-- < : Low-price products under 50,000 won
SELECT name, price FROM products
WHERE price < 50000;

-- <= : Customers with point balance of 0 or less
SELECT name, point_balance FROM customers
WHERE point_balance <= 0;

> result example:

AND / OR

AND includes the row when both conditions are true; OR includes it when at least one is true.

-- Active products priced between 100,000 and 500,000 won
SELECT name, price
FROM products
WHERE is_active = 1
  AND price >= 100000
  AND price <= 500000;

Result:

-- VIP or GOLD grade customers
SELECT name, email, grade
FROM customers
WHERE grade = 'VIP'
   OR grade = 'GOLD';

Tip: When using AND and OR together, use parentheses to clarify precedence. WHERE (grade = 'VIP' OR grade = 'GOLD') AND is_active = 1

IN

IN provides a concise way to express multiple OR conditions on the same column.

-- Retrieve GOLD or VIP customers (IN is more concise)
SELECT name, grade
FROM customers
WHERE grade IN ('GOLD', 'VIP');

Result:

-- Retrieve orders with completed statuses
SELECT order_number, status, total_amount
FROM orders
WHERE status IN ('delivered', 'confirmed', 'returned');

BETWEEN

BETWEEN is a range condition that includes both endpoints. It is equivalent to >= min AND <= max.

-- Products priced between 50,000 and 200,000 won
SELECT name, price
FROM products
WHERE price BETWEEN 50000 AND 200000;

Result:

-- Orders placed in Q1 2024
SELECT order_number, ordered_at, total_amount
FROM orders
WHERE ordered_at BETWEEN '2024-01-01' AND '2024-03-31 23:59:59';

LIKE

LIKE matches text patterns. It uses two wildcards:

% examples -- contains, starts with, ends with

-- Products with "Gaming" in the name
SELECT name, price
FROM products
WHERE name LIKE '%Gaming%';

-- Emails ending with testmail.kr domain
SELECT name, email FROM customers
WHERE email LIKE '%@testmail.kr';

-- Products starting with "삼성"
SELECT name, price FROM products
WHERE name LIKE '삼성%';

_ examples -- exactly one character

-- Customers with exactly 3-character names (1 surname + 2 given name)
SELECT name, email FROM customers
WHERE name LIKE '___';

-- Products with SKU starting with "LA-" followed by a 3-character category code
SELECT name, sku FROM products
WHERE sku LIKE 'LA-___-%';

IS NULL / IS NOT NULL

NULL means "unknown" or "absent." It is different from 0 or an empty string. For NULL comparisons, you must use IS NULL instead of = NULL.

-- Customers who have not registered their date of birth
SELECT name, email
FROM customers
WHERE birth_date IS NULL;

Result:

-- Orders that have shipping notes
SELECT order_number, notes
FROM orders
WHERE notes IS NOT NULL;

Summary

Practice Problems

Problem 1

Find female customers (gender = 'F') with SILVER or GOLD grade. Return name, grade, and point_balance.

SELECT name, grade, point_balance
FROM customers
WHERE gender = 'F'
  AND grade IN ('SILVER', 'GOLD');

Problem 2

Retrieve active products (is_active = 1) priced between 200,000 and 800,000 won. Return name and price, sorted by price descending.

SELECT name, price
FROM products
WHERE is_active = 1
  AND price BETWEEN 200000 AND 800000
ORDER BY price DESC;

Problem 3

Find customers whose gender is unknown (NULL) and who also have no last login record (last_login_at IS NULL). Return name and created_at.

SELECT name, created_at
FROM customers
WHERE gender IS NULL
  AND last_login_at IS NULL;

Problem 4

Retrieve the name and price of products priced at 1 million won or more.

SELECT name, price
FROM products
WHERE price >= 1000000;

Problem 5

Retrieve the name and stock_qty of products that are not out of stock (stock_qty <> 0).

SELECT name, stock_qty
FROM products
WHERE stock_qty <> 0;

Problem 6

From the customers table, retrieve the name and point_balance of GOLD grade customers whose point balance is between 500 and 2000.

SELECT name, point_balance
FROM customers
WHERE grade = 'GOLD'
  AND point_balance BETWEEN 500 AND 2000;

Problem 7

Retrieve the order_number and status of orders with status 'pending' or 'processing'. Use the IN operator.

SELECT order_number, status
FROM orders
WHERE status IN ('pending', 'processing');

Problem 8

Retrieve the name and price of products whose name ends with "Keyboard".

SELECT name, price
FROM products
WHERE name LIKE '%Keyboard';

Problem 9

From the staff table, retrieve the name and department of active employees (is_active = 1) whose department is not 'Sales'.

SELECT name, department
FROM staff
WHERE is_active = 1
  AND department <> 'Sales';

Problem 10

From the customers table, retrieve the name, grade, point_balance, and is_active of customers who are VIP grade and inactive (is_active = 0), or whose point balance is 5000 or more. Use parentheses to clarify the condition precedence.

SELECT name, grade, point_balance, is_active
FROM customers
WHERE (grade = 'VIP' AND is_active = 0)
   OR point_balance >= 5000;

Scoring Guide

Problem Areas:

Next: Lesson 3: Sorting and Pagination