Querying and Filtering

Retrieving exactly the data you need requires mastering SQLite's filtering, sorting, and aggregation capabilities. This lesson covers WHERE clause operators, LIKE patterns, NULL handling, aggregate functions, GROUP BY, and HAVING.

WHERE Clause Operators

-- Comparison operators
SELECT * FROM products WHERE price > 50;
SELECT * FROM products WHERE price BETWEEN 10 AND 100;

-- Logical operators
SELECT * FROM products WHERE price < 20 AND stock > 0;
SELECT * FROM products WHERE category_id = 1 OR category_id = 2;
SELECT * FROM products WHERE NOT discontinued;

-- IN list
SELECT * FROM products WHERE category_id IN (1, 3, 5);

-- NOT IN
SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM banned_users);

Pattern Matching with LIKE

-- % matches any sequence of characters
SELECT name FROM users WHERE name LIKE 'A%';      -- starts with A
SELECT name FROM users WHERE email LIKE '%@gmail.com';

-- _ matches exactly one character
SELECT code FROM products WHERE code LIKE 'SKU-___';

LIKE is case-insensitive for ASCII characters in SQLite by default. Use GLOB for case-sensitive Unix-style wildcard matching.

Handling NULL Values

NULL represents a missing or unknown value. Comparisons with NULL using = always return NULL (not true or false), so you must use IS NULL or IS NOT NULL:

-- Correct
SELECT * FROM users WHERE phone IS NULL;
SELECT * FROM users WHERE phone IS NOT NULL;

-- The COALESCE function returns the first non-NULL argument
SELECT name, COALESCE(phone, 'No phone') AS contact FROM users;

Aggregate Functions

Aggregate functions compute a single value from a set of rows:

SELECT COUNT(*)          AS total_users   FROM users;
SELECT COUNT(phone)      AS users_with_phone FROM users;  -- excludes NULLs
SELECT SUM(total)        AS revenue       FROM orders;
SELECT AVG(price)        AS avg_price     FROM products;
SELECT MIN(price), MAX(price)             FROM products;

GROUP BY

GROUP BY collapses rows with the same value into a single row, usually combined with aggregate functions:

SELECT category_id, COUNT(*) AS product_count, AVG(price) AS avg_price
FROM products
GROUP BY category_id
ORDER BY product_count DESC;

HAVING

HAVING filters groups after aggregation (while WHERE filters individual rows before aggregation):

-- Only show categories with more than 5 products
SELECT category_id, COUNT(*) AS cnt
FROM products
GROUP BY category_id
HAVING cnt > 5;

ORDER BY and LIMIT / OFFSET