Module F-3·17 min read

WHERE, ORDER BY, LIMIT, OFFSET, string functions, and date arithmetic — the tools you will use on every single query.

Written by Jatin Jain Saraf · Architect of SupraScan

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

Who this module is for: You completed F-2, can create tables and perform basic INSERT/SELECT/UPDATE/DELETE. Now you need to find specific rows, sort results, and use the built-in functions that every SQL developer relies on daily. This module covers the tools you will use in almost every query you ever write.

The `WHERE` Clause — Your Primary Filter

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

-- 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

-- 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

-- 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');

Trap: if the list (or a subquery feeding it) contains even one NULL, NOT IN silently returns zero rows — not an error, just nothing, because comparing anything to NULL with <> yields NULL, not true. NOT IN (SELECT category FROM other_table) is a classic way to hit this if that column allows NULLs. NOT EXISTS is the safer equivalent when NULLs might be involved.

`LIKE` and `ILIKE` — Pattern Matching

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

-- 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:

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

`IS NULL` and `IS NOT NULL`

-- 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`

-- 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:

-- 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.

-- 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:

-- 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

-- 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

-- 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:

-- 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

-- 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

-- 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

-- 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:

-- 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)
 
-- Postgres has no built-in TRY_CAST — a direct cast on bad input just errors:
SELECT 'not-a-number'::INTEGER;  -- ERROR: invalid input syntax for type integer
-- For safe numeric conversion, guard it with a CASE instead:
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:

-- 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:

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

-- 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

Concept	What it does
`WHERE col = val`	Exact match filter
`WHERE col BETWEEN a AND b`	Range filter (inclusive)
`WHERE col IN (a, b, c)`	Match any value in list
`WHERE col LIKE '%pattern%'`	Case-sensitive pattern match
`WHERE col ILIKE '%pattern%'`	Case-insensitive pattern match
`WHERE col IS NULL`	Check for missing value
`AND` / `OR` / `NOT`	Combine conditions
`ORDER BY col ASC/DESC`	Sort results
`LIMIT n`	Return at most n rows
`OFFSET n`	Skip first n rows
`DISTINCT`	Remove duplicate result rows
`UPPER()` / `LOWER()`	Change string case
`LENGTH()`	String length
`TRIM()`	Remove whitespace
`CONCAT()` / `\|\|`	Join strings
`NOW()`	Current timestamp
`EXTRACT()`	Get part of a date/time
`DATE_TRUNC()`	Round down to precision
`INTERVAL`	Duration arithmetic
`::type`	Type casting

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 →

Knowledge Check

Question 1: A developer writes the following SQL query to find products that are either out of stock (in_stock = false) OR have no description, AND are priced under $50:

SELECT name, price, in_stock, description
FROM products
WHERE in_stock = false OR description = NULL AND price < 50;

Which of the following statements accurately describes the issues with this query and how to correctly achieve the intended filtering?

A) The condition description = NULL is incorrect; it should be description IS NULL. Additionally, due to operator precedence, the AND clause binds tighter than OR, requiring parentheses around the OR conditions to group them correctly.
B) The condition description = NULL is correct for checking nulls. The primary issue is that OR has higher precedence than AND, leading to (in_stock = false OR description = NULL) being evaluated first.
C) The condition description = NULL is incorrect; it should be description IS NULL. However, parentheses are not needed because AND naturally groups description IS NULL with price < 50 before OR is applied.
D) The query's logic is fundamentally flawed because NULL values cannot be combined with AND or OR operators, requiring separate WHERE clauses for each condition.

Reveal Answer

Correct Answer: A

Option A is correct. The lesson explicitly states that description = NULL does not work and returns 0 rows; the correct syntax is description IS NULL. Furthermore, SQL's operator precedence dictates that AND binds tighter than OR. Without parentheses, the query in_stock = false OR description = NULL AND price < 50 is interpreted as in_stock = false OR (description = NULL AND price < 50). To achieve the intended logic of (out of stock OR no description) AND under $50, parentheses are critical: (in_stock = false OR description IS NULL) AND price < 50;. Option B is incorrect because description = NULL is wrong and AND has higher precedence than OR. Option C is incorrect because parentheses are needed to override AND's higher precedence. Option D is incorrect because NULL values can be combined with AND/OR using IS NULL/IS NOT NULL.

Question 2: A high-traffic e-commerce platform uses PostgreSQL for its product catalog. The product listing page implements pagination using LIMIT and OFFSET to display 20 products per page. The query structure is SELECT id, name, price FROM products ORDER BY created_at DESC LIMIT 20 OFFSET X;. As the platform scales to millions of products, users report that navigating to deeper pages (e.g., page 5000, where OFFSET would be 99980) becomes progressively slower, eventually leading to timeouts.

Which of the following statements best explains this performance degradation and suggests the most appropriate architectural solution for production-grade pagination?

A) The ORDER BY created_at DESC clause is the primary bottleneck because sorting millions of rows is inherently slow. Removing ORDER BY would resolve the performance issue, as LIMIT and OFFSET are optimized for speed.
B) The OFFSET clause forces the database to scan and discard a large number of rows before retrieving the desired subset. The recommended solution is cursor-based pagination, where the WHERE clause filters based on the last seen created_at value from the previous page.
C) The LIMIT clause itself is inefficient when combined with large datasets, as it requires a full table scan to determine the total number of rows before limiting. A better approach is to pre-calculate page numbers and store them in a separate index.
D) The issue is likely due to insufficient memory allocated to PostgreSQL for caching intermediate results of the OFFSET operation. Increasing work_mem and shared_buffers would mitigate the problem, allowing OFFSET to perform efficiently even at deep pages.

Reveal Answer

Correct Answer: B

Option B is correct. The lesson explicitly highlights the 'performance trap with large OFFSET', explaining that OFFSET N requires PostgreSQL to read and discard N rows before returning the requested LIMIT amount. This becomes extremely inefficient for deep pagination. The recommended solution for real-world applications is cursor-based pagination (also known as keyset pagination), which involves filtering results using a WHERE clause based on the values of the last row from the previous page (e.g., WHERE created_at < 'last_seen_timestamp' ORDER BY created_at DESC LIMIT 20). Option A is incorrect because ORDER BY is essential for consistent pagination results, and while sorting can be expensive, the primary issue described is the OFFSET's discarding behavior. Option C is incorrect; LIMIT does not require a full table scan to determine total rows, and pre-calculating page numbers is not a standard or scalable pagination strategy. Option D is incorrect; while memory tuning can help general query performance, it does not fundamentally address the algorithmic inefficiency of OFFSET discarding rows.

Question 3: A data analytics team needs to generate a report showing all unique product categories that have had at least one product added in the last 90 days. Additionally, they want to exclude any categories whose name (case-insensitive) contains the word "legacy". The final list of categories should be sorted alphabetically.

Given the products table with category (TEXT) and created_at (TIMESTAMPTZ) columns, which of the following SQL queries correctly fulfills these requirements?

A) ```sql SELECT DISTINCT category FROM products WHERE created_at > NOW() - INTERVAL '90 days' AND category NOT LIKE '%legacy%' ORDER BY category ASC;

- B) ```sql
SELECT DISTINCT category FROM products
WHERE created_at > NOW() - INTERVAL '90 days'
AND LOWER(category) NOT LIKE '%legacy%'
ORDER BY category ASC;

C) ```sql SELECT DISTINCT category FROM products WHERE created_at > NOW() - INTERVAL '90 days' AND category NOT ILIKE '%legacy%' ORDER BY category ASC;

- D) ```sql
SELECT category FROM products
WHERE created_at > NOW() - INTERVAL '90 days'
AND category NOT ILIKE '%legacy%'
GROUP BY category
ORDER BY category ASC;

Reveal Answer

Correct Answer: C

Option C is correct. It correctly uses DISTINCT category to get unique categories. The date filter created_at > NOW() - INTERVAL '90 days' accurately identifies products created in the last 90 days. Most importantly, category NOT ILIKE '%legacy%' correctly performs a case-insensitive pattern match to exclude categories containing 'legacy', as ILIKE is the PostgreSQL-specific extension for case-insensitive matching, as taught in the lesson. Finally, ORDER BY category ASC sorts the results alphabetically. Option A is incorrect because LIKE is case-sensitive, violating the 'case-insensitive' requirement. Option B achieves case-insensitivity using LOWER(category) NOT LIKE '%legacy%', which is functionally correct but ILIKE (as used in C) is the more idiomatic and often more performant way in PostgreSQL for this specific task. Option D is incorrect because while GROUP BY category would also yield unique categories, DISTINCT is the more direct and appropriate clause for simply selecting unique values of columns, especially when no aggregation is involved. GROUP BY is typically used with aggregate functions, which are covered in the next module.

Sign in to keep reading

The rest of this module is free — sign in with Google to unlock it and track your progress.

PreviousModule F-2: Tables, Rows, and the Relational Mental Model Next Module F-4: Aggregation — Summarising Your Data

Discussion

Join the discussion

Loading comments...