F-3 — Filtering, Sorting, and Finding What You Need

The WHERE Clause — Your Primary Filter

WHERE filters rows before they are returned. Only rows where the condition is TRUE are included.

sql
-- Basic equality
SELECT * FROM products WHERE category = 'peripherals';

-- Not equal (two equivalent syntaxes)
SELECT * FROM products WHERE category != 'accessories';
SELECT * FROM products WHERE category <> 'accessories';  -- SQL standard

-- Numeric comparisons
SELECT * FROM products WHERE price > 50;
SELECT * FROM products WHERE price >= 50;
SELECT * FROM products WHERE price < 50;
SELECT * FROM products WHERE price <= 50;

-- Boolean check
SELECT * FROM products WHERE in_stock = true;
SELECT * FROM products WHERE in_stock;           -- shorthand for = true
SELECT * FROM products WHERE NOT in_stock;        -- shorthand for = false

BETWEEN — Range Check

sql
-- Products priced between $25 and $75 (inclusive on both ends)
SELECT name, price FROM products
WHERE price BETWEEN 25 AND 75;

-- Equivalent to: WHERE price >= 25 AND price <= 75

BETWEEN is inclusive — it includes the boundary values.

IN — Match Any Value in a List

sql
-- Products in either of two categories
SELECT name, category FROM products
WHERE category IN ('peripherals', 'accessories');

-- Products NOT in certain categories
SELECT name, category FROM products
WHERE category NOT IN ('general', 'accessories');

LIKE and ILIKE — Pattern Matching

LIKE matches a pattern where % means "any sequence of characters" and _ means "any single character".

sql
-- Products whose name starts with 'Wire'
SELECT name FROM products WHERE name LIKE 'Wire%';
-- Matches: Wireless Mouse, Wired Headphones, etc.

-- Products whose name contains 'board'
SELECT name FROM products WHERE name LIKE '%board%';
-- Matches: Keyboard, Skateboard, Surfboard, etc.

-- Products with exactly 4 characters in the name
SELECT name FROM products WHERE name LIKE '____';
-- Each _ matches exactly one character

LIKE is case-sensitive. ILIKE is the PostgreSQL extension for case-insensitive matching:

sql
-- Finds 'keyboard', 'KEYBOARD', 'Keyboard', etc.
SELECT name FROM products WHERE name ILIKE '%keyboard%';

IS NULL and IS NOT NULL

sql
-- Products with no description
SELECT name FROM products WHERE description IS NULL;

-- Products that have a description
SELECT name FROM products WHERE description IS NOT NULL;

-- NEVER use = NULL — it does not work
SELECT name FROM products WHERE description = NULL;  -- returns 0 rows (wrong!)

Combining Conditions — AND, OR, NOT

sql
-- Both conditions must be true
SELECT name, price, category FROM products
WHERE category = 'peripherals' AND price < 100;

-- Either condition can be true
SELECT name, price, category FROM products
WHERE price < 30 OR in_stock = false;

-- Negate a condition
SELECT name, price FROM products
WHERE NOT (price > 100);

Parentheses Are Critical

Without parentheses, AND has higher precedence than OR — this catches many beginners:

sql
-- What you probably meant:
SELECT * FROM products
WHERE (category = 'peripherals' OR category = 'accessories')
  AND price < 50;

-- What this means WITHOUT parentheses (AND binds tighter than OR):
SELECT * FROM products
WHERE category = 'peripherals'
   OR (category = 'accessories' AND price < 50);
-- Returns ALL peripherals regardless of price, plus cheap accessories
-- Probably not what you wanted

Rule: when mixing AND and OR, always use parentheses to make intent explicit.

ORDER BY — Sorting Results

Without ORDER BY, PostgreSQL returns rows in no guaranteed order. Do not assume the order will be consistent between queries.

sql
-- Sort by price, cheapest first (ascending is the default)
SELECT name, price FROM products ORDER BY price;
SELECT name, price FROM products ORDER BY price ASC;  -- explicit ascending

-- Sort by price, most expensive first
SELECT name, price FROM products ORDER BY price DESC;

-- Sort by multiple columns: category first, then price within each category
SELECT name, category, price FROM products
ORDER BY category ASC, price DESC;

-- Sort by a computed value
SELECT name, price, price * 0.9 AS discounted
FROM products
ORDER BY discounted;

NULLS FIRST and NULLS LAST

When sorting a column that contains NULL, you control where nulls appear:

sql
-- Rows with NULL description appear last
SELECT name, description FROM products
ORDER BY description NULLS LAST;

-- Rows with NULL description appear first
SELECT name, description FROM products
ORDER BY description NULLS FIRST;

By default in PostgreSQL: NULL sorts last with ASC, first with DESC.

LIMIT and OFFSET — Pagination

sql
-- Get only the 5 cheapest products
SELECT name, price FROM products
ORDER BY price ASC
LIMIT 5;

-- Page 2 of results (skip first 5, take next 5)
SELECT name, price FROM products
ORDER BY price ASC
LIMIT 5 OFFSET 5;

-- Page 3
SELECT name, price FROM products
ORDER BY price ASC
LIMIT 5 OFFSET 10;

⚠️ The performance trap with large OFFSET:

OFFSET 1000 means PostgreSQL reads 1000 rows and discards them before returning your 5. At large offsets, this gets slow. For pagination in real applications, use cursor-based pagination instead (covered in later modules).

Always use ORDER BY with LIMIT — without it, the rows you get are unpredictable and vary between runs.

DISTINCT — Remove Duplicate Rows

sql
-- Get all unique categories (no duplicates)
SELECT DISTINCT category FROM products;

-- Get unique combinations of two columns
SELECT DISTINCT category, in_stock FROM products;

DISTINCT is applied across all selected columns — it removes rows where all selected columns are identical.

String Functions

These are the functions you will reach for constantly:

sql
-- Case conversion
SELECT UPPER('hello');              -- 'HELLO'
SELECT LOWER('WORLD');              -- 'world'
SELECT INITCAP('hello world');      -- 'Hello World'

-- Length
SELECT LENGTH('PostgreSQL');        -- 10
SELECT CHAR_LENGTH('hello');        -- 5 (same as LENGTH for text)

-- Trimming whitespace
SELECT TRIM('  hello  ');           -- 'hello'
SELECT LTRIM('  hello  ');          -- 'hello  ' (left trim only)
SELECT RTRIM('  hello  ');          -- '  hello' (right trim only)
SELECT TRIM('x' FROM 'xxxhelloxxx');-- 'hello' (trim specific character)

-- Padding
SELECT LPAD('42', 5, '0');          -- '00042' (pad left to length 5)
SELECT RPAD('hello', 8, '.');       -- 'hello...' (pad right to length 8)

-- Substrings
SELECT SUBSTRING('Hello World' FROM 1 FOR 5);  -- 'Hello'
SELECT LEFT('Hello World', 5);                  -- 'Hello'
SELECT RIGHT('Hello World', 5);                 -- 'World'

-- Position of a substring
SELECT POSITION('World' IN 'Hello World');   -- 7
SELECT STRPOS('Hello World', 'World');       -- 7 (same thing)

-- Replace
SELECT REPLACE('Hello World', 'World', 'PostgreSQL');  -- 'Hello PostgreSQL'

-- Concatenation (two ways)
SELECT 'Hello' || ' ' || 'World';                     -- 'Hello World'
SELECT CONCAT('Hello', ' ', 'World');                  -- 'Hello World'
SELECT CONCAT_WS(', ', 'Alice', 'Bob', 'Carol');       -- 'Alice, Bob, Carol'
-- CONCAT_WS = concat with separator; ignores NULLs

-- Split
SELECT SPLIT_PART('Alice,Bob,Carol', ',', 2);  -- 'Bob' (1-indexed)

Real-world string examples

sql
-- Find all products whose name, when lowercased, contains 'key'
SELECT name FROM products WHERE LOWER(name) LIKE '%key%';

-- Get the first word of each product name
SELECT SPLIT_PART(name, ' ', 1) AS first_word, name FROM products;

-- Format a price as a currency string
SELECT name, '$' || price::TEXT AS formatted_price FROM products;
-- '::TEXT' converts numeric to text for concatenation

Date and Time Functions

sql
-- Current date and time
SELECT NOW();               -- '2026-05-17 10:30:45.123456+00' (TIMESTAMPTZ)
SELECT CURRENT_TIMESTAMP;   -- same as NOW()
SELECT CURRENT_DATE;        -- '2026-05-17' (DATE only)
SELECT CURRENT_TIME;        -- '10:30:45.123456+00' (TIME only)

-- Extract parts of a date/time
SELECT EXTRACT(YEAR   FROM NOW());    -- 2026
SELECT EXTRACT(MONTH  FROM NOW());    -- 5
SELECT EXTRACT(DAY    FROM NOW());    -- 17
SELECT EXTRACT(HOUR   FROM NOW());    -- 10
SELECT EXTRACT(MINUTE FROM NOW());    -- 30
SELECT EXTRACT(DOW    FROM NOW());    -- 0=Sunday, 1=Monday ... 6=Saturday

-- Truncate to a precision
SELECT DATE_TRUNC('month', NOW());    -- '2026-05-01 00:00:00+00'
SELECT DATE_TRUNC('year',  NOW());    -- '2026-01-01 00:00:00+00'
SELECT DATE_TRUNC('day',   NOW());    -- '2026-05-17 00:00:00+00'
SELECT DATE_TRUNC('hour',  NOW());    -- '2026-05-17 10:00:00+00'

-- Age / difference between two timestamps
SELECT AGE(NOW(), '2024-01-01');
-- '2 years 4 mons 16 days 10:30:45.123456'

SELECT NOW() - '2024-01-01'::TIMESTAMPTZ;
-- '869 days 10:30:45.123456'

Date arithmetic

sql
-- Add/subtract intervals
SELECT NOW() + INTERVAL '7 days';        -- one week from now
SELECT NOW() - INTERVAL '30 days';       -- 30 days ago
SELECT NOW() + INTERVAL '2 hours 30 minutes';
SELECT '2026-05-17'::DATE + 30;          -- add 30 days to a date

-- Find records from the last 7 days
SELECT * FROM products
WHERE created_at > NOW() - INTERVAL '7 days';

-- Find records created this month
SELECT * FROM products
WHERE DATE_TRUNC('month', created_at) = DATE_TRUNC('month', NOW());

-- Find records from a specific date range
SELECT * FROM products
WHERE created_at BETWEEN '2026-05-01' AND '2026-05-31 23:59:59';

Type Casting — Converting Between Types

Sometimes you need to explicitly convert a value from one type to another:

sql
-- Two syntaxes for casting
SELECT '42'::INTEGER;                  -- text to integer
SELECT CAST('42' AS INTEGER);          -- same thing, more verbose

-- Practical uses
SELECT '2026-05-17'::DATE;             -- text to date
SELECT '10:30:00'::TIME;               -- text to time
SELECT '129.99'::NUMERIC;             -- text to numeric
SELECT 42::TEXT;                       -- integer to text (for concatenation)

-- Safe casting (returns NULL instead of error on failure)
SELECT NULLIF('not-a-number', 'not-a-number');  -- NULL if both arguments equal
-- For safe numeric conversion, use a CASE or TRY_CAST equivalent:
SELECT CASE WHEN '42' ~ '^\d+$' THEN '42'::INTEGER ELSE NULL END;

Practical Exercise: Product Search Queries

Using the products table from F-2, answer these questions with SQL:

sql
-- Q1: Find all products priced between $25 and $75
SELECT name, price FROM products
WHERE price BETWEEN 25 AND 75
ORDER BY price;

-- Q2: Find all in-stock peripherals, most expensive first
SELECT name, price FROM products
WHERE category = 'peripherals' AND in_stock = true
ORDER BY price DESC;

-- Q3: Find products with 'USB' or 'Wireless' in the name (case-insensitive)
SELECT name, price FROM products
WHERE name ILIKE '%usb%' OR name ILIKE '%wireless%';

-- Q4: Find products that have no description
SELECT name, category FROM products
WHERE description IS NULL;

-- Q5: Get the 3 cheapest in-stock products
SELECT name, price FROM products
WHERE in_stock = true
ORDER BY price ASC
LIMIT 3;

-- Q6: Get all unique categories
SELECT DISTINCT category FROM products ORDER BY category;

-- Q7: Find products where the name, when uppercased, starts with 'W'
SELECT name FROM products WHERE UPPER(name) LIKE 'W%';

-- Q8: Find products created in the last 30 days
SELECT name, created_at FROM products
WHERE created_at > NOW() - INTERVAL '30 days';

-- Q9: Show product names alongside a formatted price with currency symbol
SELECT name, '$' || price::TEXT AS price_display FROM products;

-- Q10: Find products where price ends in .99
SELECT name, price FROM products
WHERE price::TEXT LIKE '%.99';

Putting It All Together

A query can chain all of these together. The order of clauses is:

sql
SELECT   [columns or expressions]
FROM     [table]
WHERE    [filter conditions]
ORDER BY [sort columns]
LIMIT    [max rows]
OFFSET   [rows to skip];

sql
-- A complete query: in-stock accessories under $50,
-- sorted by price, page 1 (first 3 results)
SELECT
  name,
  INITCAP(category) AS category,
  '$' || price::TEXT AS price,
  in_stock
FROM products
WHERE category = 'accessories'
  AND in_stock = true
  AND price < 50
ORDER BY price ASC
LIMIT 3
OFFSET 0;

Summary

Module F-4 covers aggregation — GROUP BY, HAVING, COUNT, SUM, AVG, and the mental model that trips up almost every beginner learning SQL.

Next: F-4 — Aggregation — Summarising Your Data →