Forem: Vivek Kumar

SQL LIMIT and OFFSET: Paginate Your Query Results Like a Pro

Vivek Kumar — Wed, 15 Apr 2026 07:41:25 +0000

Every app that shows lists of data — a product catalog, a blog feed, a leaderboard — needs pagination. And at the heart of SQL-based pagination sit two deceptively simple keywords: LIMIT and OFFSET. If you've ever wondered how websites show you "Page 1 of 47" or a "Load more" button, this is the mechanism behind it.

In this guide we'll cover exactly what LIMIT and OFFSET do, how to use them correctly, and — just as importantly — where they can bite you when your dataset grows.

What Problem Do LIMIT and OFFSET Solve?

Imagine your products table has 50,000 rows. If you run a bare SELECT * FROM products, your database has to send all 50,000 rows to your application. That's slow, wastes memory, and overwhelms users who only want to see the first 20 items.

LIMIT caps how many rows the database returns. OFFSET tells it where to start.

Basic Syntax

SELECT column1, column2
FROM table_name
ORDER BY some_column
LIMIT number_of_rows
OFFSET rows_to_skip;

Note: MySQL also supports a shorthand: LIMIT offset, count — but the two-argument form can be confusing because the order is reversed from what you might expect. Using the explicit LIMIT … OFFSET … form is clearer and works in PostgreSQL, SQLite, and MySQL alike.

Example: A Simple Product Catalog

Let's say we have this table:

CREATE TABLE products (
    id          SERIAL PRIMARY KEY,
    name        VARCHAR(100),
    category    VARCHAR(50),
    price       NUMERIC(10, 2),
    created_at  TIMESTAMP DEFAULT NOW()
);

Get the first 10 products, sorted by price:

SELECT id, name, category, price
FROM products
ORDER BY price ASC
LIMIT 10;

id	name	category	price
42	USB-C Cable 1m	Accessories	4.99
7	Microfiber Cloth	Accessories	5.49
19	AA Batteries (4pk)	Electronics	6.99
…7 more rows…

Get the second page (rows 11–20):

SELECT id, name, category, price
FROM products
ORDER BY price ASC
LIMIT 10
OFFSET 10;

Get the third page (rows 21–30):

SELECT id, name, category, price
FROM products
ORDER BY price ASC
LIMIT 10
OFFSET 20;

The pattern is simple: OFFSET = (page_number - 1) * page_size.

Real-World Example: Blog Post Feed

Here's what this looks like in a blog application with a posts table:

CREATE TABLE posts (
    id         SERIAL PRIMARY KEY,
    title      VARCHAR(200),
    author_id  INT,
    published  BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT NOW()
);

Fetch page 3 of published posts, 5 per page, newest first:

SELECT id, title, created_at
FROM posts
WHERE published = TRUE
ORDER BY created_at DESC
LIMIT 5
OFFSET 10;

You can even make this dynamic. In application code you'd pass the page number as a parameter:

-- Page number: 5, Page size: 20
SELECT id, title, created_at
FROM posts
WHERE published = TRUE
ORDER BY created_at DESC
LIMIT 20
OFFSET 80;   -- (5 - 1) * 20 = 80

Common Mistakes and Gotchas

1. Forgetting ORDER BY

This is the most important rule: always pair LIMIT/OFFSET with ORDER BY.

Without ORDER BY, the database can return rows in any order — and that order can change between queries. Your "page 2" might contain rows that also appeared on "page 1".

-- ❌ BAD — results are unpredictable
SELECT * FROM products LIMIT 10 OFFSET 10;

-- ✅ GOOD — deterministic ordering
SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 10;

2. OFFSET Doesn't Skip Work — It Just Throws It Away

Here's the performance trap that surprises many developers: OFFSET 10000 doesn't mean the database starts reading from row 10,001. It means the database reads all 10,001 rows and then discards the first 10,000 before returning results to you.

For small datasets this is fine. For large datasets this becomes very slow:

-- This query reads and discards 990,000 rows, then returns 10
SELECT id, title FROM posts ORDER BY created_at DESC LIMIT 10 OFFSET 990000;

For most beginner use cases (tables with a few thousand rows), this is not a problem. But it's good to know for when your app grows.

3. LIMIT Without a Number Is Invalid in Standard SQL

-- ❌ INVALID — can't use OFFSET without LIMIT in most databases
SELECT * FROM products OFFSET 5;

-- ✅ Use a very large number if you want "all rows starting from offset"
SELECT * FROM products ORDER BY id OFFSET 5 LIMIT ALL;  -- PostgreSQL only

4. Counting Total Pages Requires a Separate Query

LIMIT and OFFSET only return a slice of data. To show "Page 3 of 47", you need a separate count:

-- Get total count for pagination controls
SELECT COUNT(*) AS total_posts
FROM posts
WHERE published = TRUE;

-- Then compute: total_pages = CEIL(total_posts / page_size)

Putting It All Together: A Complete Pagination Example

Here's a realistic scenario: a comments section on a news site, showing 10 comments per page.

-- Schema
CREATE TABLE comments (
    id         SERIAL PRIMARY KEY,
    post_id    INT NOT NULL,
    author     VARCHAR(100),
    body       TEXT,
    upvotes    INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Page 1: top-voted comments on post #42
SELECT id, author, body, upvotes
FROM comments
WHERE post_id = 42
ORDER BY upvotes DESC, created_at DESC
LIMIT 10
OFFSET 0;

-- Page 2
SELECT id, author, body, upvotes
FROM comments
WHERE post_id = 42
ORDER BY upvotes DESC, created_at DESC
LIMIT 10
OFFSET 10;

Notice the double ORDER BY — we sort by votes first (so the best comments appear first), then by date as a tiebreaker. This makes results stable and predictable.

Quick Reference

Goal	SQL snippet
First N rows	`LIMIT N`
Skip first N rows, take next M	`LIMIT M OFFSET N`
Page P with page size S	`LIMIT S OFFSET (P-1)*S`
Count total rows (for page count)	`SELECT COUNT(*) FROM …`

Key Takeaways

LIMIT controls how many rows are returned. OFFSET controls where in the result set to start.
Always use ORDER BY with LIMIT/OFFSET — without it, results are non-deterministic.
The formula for page-based pagination is OFFSET = (page - 1) * page_size.
OFFSET can become slow on very large datasets because the database still reads (and discards) all skipped rows — but for most beginner use cases, this won't be an issue.
Always run a separate COUNT(*) query to know the total number of pages.

LIMIT and OFFSET are among the first tools you'll reach for when building real applications with SQL. They're simple, powerful, and supported by virtually every relational database out there.

Have you hit any pagination bugs in your own projects? Drop them in the comments — I'd love to hear what tripped you up and how you solved it! 🚀

SQL Indexes Explained: Write Queries That Don't Make Your Database Cry

Vivek Kumar — Mon, 13 Apr 2026 17:41:34 +0000

SQL Indexes Explained: Write Queries That Don't Make Your Database Cry

Every developer eventually hits the moment where a query that looked perfectly fine suddenly takes 12 seconds to run in production. You stare at it in disbelief — it's not that complicated! Then someone suggests adding an index, you do, and the query drops to 20ms. Magic? Not quite. Once you understand what indexes actually do, you'll know exactly when to add them, how to design them well, and when not to use them.

This guide is for developers who write SQL regularly but want to move beyond "just add an index" to actually understanding the mechanics. We'll cover B-tree indexes, composite indexes, covering indexes, and the common mistakes that leave performance on the table — using PostgreSQL syntax throughout (MySQL and SQL Server are nearly identical).

What Is an Index, Really?

An index is a separate data structure that the database maintains alongside your table. Think of it like the index at the back of a textbook: instead of reading every page to find "normalization," you look it up alphabetically and jump straight to the right page.

Without an index, the database does a sequential scan — it reads every row in the table to find what you need. For a table with 10 rows, that's fine. For a table with 10 million rows, it's painful.

-- Without an index on email, this scans every row
SELECT * FROM users WHERE email = 'alice@example.com';

With an index on email, the database uses a tree structure to find the matching row in a fraction of the time.

B-Tree: The Default Index Type

When you create an index without specifying a type, you get a B-tree (balanced tree). B-tree indexes store values in sorted order, organized as a tree where each level narrows down the search. They work brilliantly for:

Exact matches: WHERE status = 'active'
Range queries: WHERE created_at >= '2025-01-01'
Prefix searches: WHERE name LIKE 'Smi%'
Sorting: ORDER BY last_name

Creating a Simple Index

-- Syntax
CREATE INDEX idx_users_email ON users (email);

-- Confirm it exists
\d users   -- in psql, or:
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'users';

Now the query below uses the index instead of scanning the table:

EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'alice@example.com';

You'll see something like Index Scan using idx_users_email in the query plan — a good sign.

Composite Indexes: Indexing Multiple Columns

A composite (multi-column) index covers more than one column in a single index structure. This is where index design gets interesting.

-- A composite index for order lookups by customer and date
CREATE INDEX idx_orders_customer_date ON orders (customer_id, created_at);

The critical rule: the leftmost prefix principle.

The database can only use a composite index if the query filters on the leftmost column(s) in order. Think of it like a phone book sorted by last name, then first name: you can find everyone named "Smith", or find "John Smith" — but you can't efficiently search for everyone named "John" without scanning the whole book.

-- ✅ Uses idx_orders_customer_date (filters on customer_id)
SELECT * FROM orders WHERE customer_id = 42;

-- ✅ Uses idx_orders_customer_date (filters on both columns in order)
SELECT * FROM orders WHERE customer_id = 42 AND created_at >= '2025-01-01';

-- ❌ Cannot use the index (skips customer_id, goes straight to created_at)
SELECT * FROM orders WHERE created_at >= '2025-01-01';

For the last query, you'd need a separate index on created_at alone.

General rule: Put equality columns first, range columns last.

-- Good: filter by status (equality) first, then date range
CREATE INDEX idx_orders_status_date ON orders (status, created_at);

-- A query that benefits
SELECT * FROM orders WHERE status = 'pending' AND created_at >= NOW() - INTERVAL '7 days';

Covering Indexes: Eliminating Table Lookups

Even after finding the matching rows in an index, the database usually has to go back to the main table to fetch the columns you actually need. This is called a heap lookup (or table lookup). For very high-frequency queries, you can eliminate this extra step with a covering index — an index that contains all the columns your query needs.

-- Without covering index: finds row IDs in index, then fetches full rows from table
SELECT email, full_name FROM users WHERE status = 'active';

-- Create a covering index that includes the SELECT columns
CREATE INDEX idx_users_status_covering ON users (status) INCLUDE (email, full_name);
-- (INCLUDE available in PostgreSQL 11+, SQL Server 2005+)

With this index, the database can answer the entire query from the index alone — never touching the main table. This is called an index-only scan and is extremely fast for read-heavy queries.

You can verify it worked with:

EXPLAIN ANALYZE SELECT email, full_name FROM users WHERE status = 'active';
-- Look for "Index Only Scan" in the output

Partial Indexes: Index Only What You Query

If you only ever query a subset of rows, a partial index (PostgreSQL) lets you index just that subset:

-- Only index orders that are still pending (the ones you actually look up)
CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status = 'pending';

This index is much smaller and faster to maintain than a full index on created_at. It's perfect for tables where most queries target a minority of rows — think "active users", "unprocessed jobs", or "open tickets".

Common Indexing Mistakes

1. Over-Indexing

Every index speeds up reads but slows down writes. When you INSERT, UPDATE, or DELETE a row, the database must update every index on that table. A table with 12 indexes on a write-heavy workload is going to hurt.

-- Anti-pattern: indexes on every column "just in case"
CREATE INDEX idx_users_first_name ON users (first_name);
CREATE INDEX idx_users_last_name  ON users (last_name);
CREATE INDEX idx_users_phone      ON users (phone);
CREATE INDEX idx_users_created_at ON users (created_at);
-- ...and five more you never use

-- Better: only index columns that appear in WHERE, JOIN, or ORDER BY
-- for high-traffic queries

Audit your indexes regularly. Most databases let you check whether an index is actually being used.

2. LIKE Wildcards Defeat Indexes

A trailing wildcard (LIKE 'Smi%') can use an index. A leading wildcard (LIKE '%smith') cannot — it forces a full scan because the index is sorted by the start of the string, not the end.

-- ✅ Can use an index on last_name
WHERE last_name LIKE 'Smi%'

-- ❌ Forces a full scan — index is useless here
WHERE last_name LIKE '%smith'

For full-text searching within strings, consider a full-text index or a tool like Elasticsearch instead.

3. Functions on Indexed Columns Kill the Index

Wrapping an indexed column in a function prevents the optimizer from using the index:

-- ❌ The function LOWER() breaks index usage on email
WHERE LOWER(email) = 'alice@example.com'

-- ✅ Option 1: Normalize data at insert time (store email already lowercased)
-- ✅ Option 2: Create a functional index
CREATE INDEX idx_users_email_lower ON users (LOWER(email));
WHERE LOWER(email) = 'alice@example.com'  -- now uses the functional index

The same applies to DATE(timestamp_col), YEAR(date_col), and similar functions.

4. Low-Cardinality Columns

A column like is_active (only TRUE or FALSE) is a poor candidate for a B-tree index — the optimizer may decide a full scan is faster because the index doesn't narrow things down much. Partial indexes (as shown above) are a better choice for low-cardinality filtering.

How to Check If Your Index Is Being Used

Always verify with EXPLAIN ANALYZE:

EXPLAIN ANALYZE
SELECT order_id, total_amount
FROM orders
WHERE customer_id = 42
  AND created_at >= '2025-01-01';

Look for these keywords in the output:

Output keyword	Meaning	Good?
Index Scan	Found rows via index, then fetched from table	✅
Index Only Scan	Answered entirely from the index (covering index)	✅✅
Bitmap Index Scan	Built a bitmap of matching rows from index	✅
Seq Scan	Full table scan — index not used	⚠️ investigate

If you see a Seq Scan on a large table and you have an index, something is wrong: the query may not match the index's column order, a function may be wrapping the column, or the table might be too small for the optimizer to bother.

Key Takeaways

Indexing well is one of the highest-leverage skills in SQL. Here's the short version:

B-tree indexes are the default and work for equality, range, prefix, and sort queries
Composite indexes follow the leftmost prefix rule — equality columns first, range columns last
Covering indexes (with INCLUDE) eliminate heap lookups for read-heavy queries
Partial indexes keep things lean when you only query a known subset of rows
Every index has a write overhead — index strategically, not speculatively
Functions on indexed columns break index usage — use functional indexes or normalize data at write time
Always verify with EXPLAIN ANALYZE — never assume an index is being used

A well-indexed table can handle orders of magnitude more load without any code changes. And once you start reading EXPLAIN ANALYZE output comfortably, you'll have a superpower that most developers don't.

Which indexing trick has saved you the most query time? Drop a comment — I'd love to hear about real production wins (or the horror stories that led to them). If this was useful, share it with a colleague who's still hitting query timeouts!

Write Your SQL Like a Pro: Mastering Common Table Expressions (CTEs)

Vivek Kumar — Fri, 10 Apr 2026 17:50:13 +0000

Write Your SQL Like a Pro: Mastering Common Table Expressions (CTEs)

If you've ever stared at a deeply nested SQL query and wondered who wrote that monster — chances are it could have been written much more cleanly with a CTE.

Common Table Expressions (CTEs) are one of the most powerful readability and maintainability tools in SQL. They let you break a complex query into named, logical building blocks that read almost like a story. Once you start using them, you'll wonder how you ever got along without them.

In this guide, you'll learn what CTEs are, how to write them, how to chain multiple CTEs together, and some real-world scenarios where they shine.

What Is a CTE?

A CTE — defined with the WITH keyword — is a temporary named result set that exists only for the duration of a single query. Think of it like a temporary view you define at the top of your query and then reference below.

Basic syntax:

WITH cte_name AS (
    SELECT ...
    FROM ...
    WHERE ...
)
SELECT *
FROM cte_name;

That's it. You define the CTE inside parentheses after AS, name it, and then use it in the main query below like any regular table.

Your First CTE: A Simple Example

Say you have an orders table and you want to find all customers who placed more than 5 orders. You could write this as a subquery:

-- Subquery approach (harder to read)
SELECT customer_id, order_count
FROM (
    SELECT customer_id, COUNT(*) AS order_count
    FROM orders
    GROUP BY customer_id
) AS order_summary
WHERE order_count > 5;

Here's the same logic as a CTE:

-- CTE approach (much easier to read)
WITH order_summary AS (
    SELECT customer_id, COUNT(*) AS order_count
    FROM orders
    GROUP BY customer_id
)
SELECT customer_id, order_count
FROM order_summary
WHERE order_count > 5;

Both queries return the same result, but the CTE version makes the intent clear immediately. The named block order_summary tells you exactly what it contains before you even read it.

Chaining Multiple CTEs

Here's where CTEs get really powerful: you can define multiple CTEs in a single query, each building on the previous. Separate them with commas.

Let's say you're working with an e-commerce database and you want to find the top 10 customers by revenue, but only count orders that were actually delivered (not cancelled or refunded).

Tables:

orders(order_id, customer_id, status, created_at)
order_items(order_id, product_id, quantity, unit_price)
customers(customer_id, name, email, country)

WITH 
-- Step 1: Only look at delivered orders
delivered_orders AS (
    SELECT order_id, customer_id
    FROM orders
    WHERE status = 'delivered'
),

-- Step 2: Calculate revenue per order
order_revenue AS (
    SELECT 
        do.customer_id,
        SUM(oi.quantity * oi.unit_price) AS revenue
    FROM delivered_orders do
    JOIN order_items oi ON oi.order_id = do.order_id
    GROUP BY do.customer_id
),

-- Step 3: Rank customers by total revenue
ranked_customers AS (
    SELECT 
        or.customer_id,
        or.revenue,
        RANK() OVER (ORDER BY or.revenue DESC) AS revenue_rank
    FROM order_revenue or
)

-- Final query: Get the top 10
SELECT 
    c.name,
    c.email,
    rc.revenue,
    rc.revenue_rank
FROM ranked_customers rc
JOIN customers c ON c.customer_id = rc.customer_id
WHERE rc.revenue_rank <= 10
ORDER BY rc.revenue_rank;

Notice how each CTE has one clear job. delivered_orders filters, order_revenue aggregates, ranked_customers ranks. Reading this query top to bottom tells the story of what you're doing — no nested parentheses archaeology required.

Using CTEs with UPDATE and DELETE

CTEs aren't just for SELECT queries. You can use them with UPDATE and DELETE too, which is incredibly handy for complex conditional modifications.

Example: Archive orders older than 2 years

WITH old_orders AS (
    SELECT order_id
    FROM orders
    WHERE created_at < NOW() - INTERVAL '2 years'
      AND status IN ('delivered', 'cancelled')
)
DELETE FROM orders
WHERE order_id IN (SELECT order_id FROM old_orders);

Or with UPDATE — say you want to flag high-value customers:

WITH high_value_customers AS (
    SELECT customer_id
    FROM orders
    WHERE status = 'delivered'
    GROUP BY customer_id
    HAVING SUM(total_amount) > 10000
)
UPDATE customers
SET tier = 'platinum'
WHERE customer_id IN (SELECT customer_id FROM high_value_customers);

Real-World Use Case: Running Totals

CTEs pair beautifully with window functions. Here's a classic example — a running total of daily sales:

WITH daily_sales AS (
    SELECT 
        DATE(created_at) AS sale_date,
        SUM(total_amount) AS daily_total
    FROM orders
    WHERE status = 'delivered'
    GROUP BY DATE(created_at)
)
SELECT 
    sale_date,
    daily_total,
    SUM(daily_total) OVER (ORDER BY sale_date) AS running_total
FROM daily_sales
ORDER BY sale_date;

Sample output:

sale_date	daily_total	running_total
2026-01-01	4200.00	4200.00
2026-01-02	3800.00	8000.00
2026-01-03	5100.00	13100.00

CTEs vs. Subqueries: When to Use Which?

Feature	CTE	Subquery
Readability	✅ Much easier for complex logic	❌ Can get deeply nested
Reusability within query	✅ Reference multiple times	❌ Must repeat the query
Recursive queries	✅ Supported	❌ Not supported
Performance	Roughly equivalent	Roughly equivalent
Short, simple filters	🤷 Overkill	✅ Fine for one-liners

The general rule: if your subquery is more than 5–6 lines, or you need to reference it more than once, use a CTE.

Common Mistakes to Avoid

1. Trying to reference a CTE outside its query

CTEs only live within the single statement they're defined in. You cannot define a CTE in one query and use it in another.

-- ❌ This does NOT work
WITH my_cte AS (SELECT * FROM products)

-- ... some other SQL here ...

SELECT * FROM my_cte; -- ERROR: my_cte doesn't exist here

2. Forgetting the comma between multiple CTEs

-- ❌ Missing comma — will throw a syntax error
WITH cte_one AS (
    SELECT ...
)
cte_two AS (   -- needs a comma after the closing parenthesis above
    SELECT ...
)
SELECT * FROM cte_two;

-- ✅ Correct
WITH cte_one AS (
    SELECT ...
),
cte_two AS (
    SELECT ...
)
SELECT * FROM cte_two;

3. Overusing CTEs for simple queries

CTEs add clarity, not magic. For a simple single-table SELECT, a CTE just adds boilerplate. Use them when they actually help readability.

Key Takeaways

CTEs are defined with the WITH keyword and give you a named, temporary result set
They dramatically improve readability for complex queries
You can chain multiple CTEs in a single query, each building on the last
CTEs work with SELECT, INSERT, UPDATE, and DELETE
They're the natural home for queries you'd otherwise write as deeply nested subqueries
Performance is roughly equivalent to subqueries in most modern databases (PostgreSQL, MySQL 8+, SQL Server, etc.)

What's Next?

Once you're comfortable with regular CTEs, the next step is recursive CTEs — which allow a CTE to call itself, making them perfect for traversing hierarchical data like org charts, category trees, or folder structures. That's a topic for another day, but knowing that CTEs can be recursive shows just how powerful this feature really is.

Have you been using CTEs in your projects? Drop a comment below — I'd love to hear about any creative use cases you've found. And if this cleared up any confusion, share it with a teammate who's still writing those 10-level-deep subqueries! 😅

SQL CASE Expressions: Write Smarter Queries with Conditional Logic

Vivek Kumar — Fri, 10 Apr 2026 17:44:55 +0000

SQL CASE expressions are one of those features that seem simple at first glance — "just an if/else in SQL, right?" — but once you start using them beyond the basics, you realize they're one of the most versatile tools in your query-writing arsenal. Whether you're bucketing numeric data, computing dynamic labels, pivoting rows into columns, or embedding business logic directly into your SELECT, CASE does the heavy lifting without any application-layer gymnastics.

In this guide, we'll go from the fundamentals to genuinely useful patterns you can drop into real production queries today.

The Two Flavors: Simple vs. Searched CASE

Before jumping into examples, it's worth understanding that CASE comes in two syntactic forms.

Simple CASE

A simple CASE compares one expression against a list of values:

SELECT
  order_id,
  status,
  CASE status
    WHEN 'pending'    THEN 'Awaiting Payment'
    WHEN 'confirmed'  THEN 'Processing'
    WHEN 'shipped'    THEN 'On Its Way'
    WHEN 'delivered'  THEN 'Complete'
    ELSE 'Unknown'
  END AS status_label
FROM orders;

Clean and readable when you're mapping discrete values. The ELSE clause is your safety net for anything not explicitly listed — skip it and SQL returns NULL for unmatched rows (which is often a silent bug).

Searched CASE

A searched CASE evaluates a boolean condition per branch — much more flexible:

SELECT
  product_name,
  price,
  CASE
    WHEN price < 10.00  THEN 'Budget'
    WHEN price < 50.00  THEN 'Mid-Range'
    WHEN price < 200.00 THEN 'Premium'
    ELSE 'Luxury'
  END AS price_tier
FROM products;

Each WHEN clause is evaluated top-to-bottom and SQL stops at the first match. This matters: if a product costs $8, it hits 'Budget' and the rest are never checked. Structure your conditions from most specific to least specific (or lowest to highest) to avoid unexpected results.

Real-World Pattern 1: Data Bucketing in Reports

Say you run an e-commerce platform and want a quick breakdown of customer lifetime value:

SELECT
  customer_id,
  total_spent,
  CASE
    WHEN total_spent >= 1000 THEN 'VIP'
    WHEN total_spent >= 500  THEN 'Gold'
    WHEN total_spent >= 100  THEN 'Silver'
    ELSE 'Standard'
  END AS customer_tier,
  CASE
    WHEN last_order_date >= CURRENT_DATE - INTERVAL '30 days' THEN 'Active'
    WHEN last_order_date >= CURRENT_DATE - INTERVAL '90 days' THEN 'At Risk'
    ELSE 'Churned'
  END AS engagement_status
FROM customers;

Two CASE expressions in the same SELECT, each adding a different dimension of classification. This logic lives in the database layer, which means your application code stays clean and your BI tool can use it directly.

Real-World Pattern 2: Conditional Aggregation (This One Is Gold)

This is where CASE earns its reputation among intermediate SQL developers. By combining CASE with aggregate functions like SUM and COUNT, you can pivot row-level data into columnar summaries — without a separate PIVOT clause.

Suppose you have a sales table and you want monthly totals broken out by region in a single row per month:

SELECT
  DATE_TRUNC('month', sale_date) AS month,
  SUM(CASE WHEN region = 'North America' THEN revenue ELSE 0 END) AS na_revenue,
  SUM(CASE WHEN region = 'Europe'        THEN revenue ELSE 0 END) AS eu_revenue,
  SUM(CASE WHEN region = 'Asia Pacific'  THEN revenue ELSE 0 END) AS apac_revenue,
  COUNT(CASE WHEN region = 'North America' AND revenue > 10000 THEN 1 END) AS na_big_deals
FROM sales
GROUP BY DATE_TRUNC('month', sale_date)
ORDER BY month;

Notice the COUNT trick at the end: when the condition is false, CASE returns NULL, and COUNT ignores NULLs — so you're effectively counting only the rows that match. No subquery, no CTE, just clean conditional aggregation.

The result looks something like this:

month	na_revenue	eu_revenue	apac_revenue	na_big_deals
2026-01-01	142,500	98,300	67,200	8
2026-02-01	165,700	110,400	72,100	11
2026-03-01	158,200	104,800	81,500	9

Real-World Pattern 3: CASE in ORDER BY for Custom Sort Logic

Standard ORDER BY sorts numerically or alphabetically. What if your business has a specific priority order that doesn't follow those rules?

SELECT
  ticket_id,
  title,
  priority,
  created_at
FROM support_tickets
WHERE status = 'open'
ORDER BY
  CASE priority
    WHEN 'critical'  THEN 1
    WHEN 'high'      THEN 2
    WHEN 'medium'    THEN 3
    WHEN 'low'       THEN 4
    ELSE 5
  END ASC,
  created_at ASC;

Critical tickets bubble to the top, then within the same priority, older tickets come first. This is much cleaner than trying to encode order into the priority column itself, and it's completely transparent to anyone reading the query.

Real-World Pattern 4: CASE in WHERE Clauses (Dynamic Filters)

You can even use CASE inside a WHERE clause for conditional filtering logic — useful when you're writing parameterized queries or stored procedures:

-- Show all orders, but if a user_id is provided, filter to that user only
SELECT
  order_id,
  user_id,
  total,
  created_at
FROM orders
WHERE
  CASE
    WHEN :filter_user_id IS NOT NULL THEN user_id = :filter_user_id
    ELSE TRUE
  END;

Note: Syntax varies by database. In PostgreSQL and most modern databases this pattern works well. In MySQL you might use (:filter_user_id IS NULL OR user_id = :filter_user_id) instead, which is actually more readable — but CASE in WHERE is a valid approach in procedural SQL contexts.

Common Mistakes & Gotchas

1. Forgetting that ELSE defaults to NULL

-- If status is anything besides 'active' or 'inactive', this returns NULL
CASE status
  WHEN 'active'   THEN 'Enabled'
  WHEN 'inactive' THEN 'Disabled'
END

If NULL showing up in your results or aggregates is not what you want, always add an explicit ELSE clause.

2. Type mismatches across branches

All THEN branches must return compatible data types. Mixing 'Yes' (text) with 1 (integer) in different branches will cause an error in strict databases like PostgreSQL. Be consistent:

-- Bad: mixed types
CASE WHEN is_premium = true THEN 'yes' ELSE 0 END

-- Good: consistent types
CASE WHEN is_premium = true THEN 'yes' ELSE 'no' END

3. Using CASE when COALESCE or IIF is cleaner

For simple null-handling, COALESCE is much more readable:

-- Verbose
CASE WHEN nickname IS NULL THEN first_name ELSE nickname END

-- Better
COALESCE(nickname, first_name)

Save CASE for situations that actually need multiple conditions or more complex logic.

4. Short-circuit evaluation is NOT guaranteed in all databases

While most databases evaluate CASE branches top-to-bottom and stop at the first match, do not rely on CASE to prevent division-by-zero errors in a way that assumes strict short-circuiting. Some query optimizers may reorder operations. Use explicit guards:

-- Safer approach for division
CASE
  WHEN total_sessions > 0 THEN conversions::float / total_sessions
  ELSE NULL
END AS conversion_rate

CASE Inside Window Functions

One of the more powerful combinations: using CASE inside a window function to do conditional running totals or ranks:

SELECT
  order_id,
  customer_id,
  revenue,
  SUM(CASE WHEN is_refund = false THEN revenue ELSE 0 END)
    OVER (PARTITION BY customer_id ORDER BY order_date) AS running_net_revenue
FROM orders;

This gives you a per-customer running total of net revenue (excluding refunds), all in a single pass over the data.

Key Takeaways

Simple CASE maps one expression to a set of values; Searched CASE evaluates arbitrary boolean conditions — use whichever fits the situation.
Always include an ELSE clause unless you intentionally want unmatched rows to return NULL.
Conditional aggregation (SUM(CASE WHEN ... THEN value ELSE 0 END)) is one of the most practical CASE patterns in real analytics work.
You can use CASE in SELECT, WHERE, ORDER BY, HAVING, and inside aggregate/window functions.
Keep branches type-consistent to avoid runtime errors.
Don't overuse CASE — COALESCE, NULLIF, and IIF (SQL Server) handle simpler scenarios more cleanly.

CASE expressions sit at the intersection of SQL's set-based thinking and the conditional logic developers are already comfortable with from application code. Master them and you'll find yourself writing fewer subqueries, doing less post-processing in your application layer, and generally producing queries that other engineers can read and maintain without a translation guide.

Have a favorite CASE trick I didn't cover? Drop it in the comments — I'd love to see how others are using conditional logic in their queries!

SQL Aggregate Functions: Stop Guessing, Start Calculating

Vivek Kumar — Thu, 09 Apr 2026 18:20:27 +0000

SQL Aggregate Functions: Stop Guessing, Start Calculating

If you've ever needed to answer questions like "How many orders did we get this month?", "What's our total revenue?", or "Who's our highest-paid employee?" — you need SQL aggregate functions. They're the workhorses of data analysis, and once you understand them, you'll wonder how you ever got by without them.

In this guide, we'll cover the five essential SQL aggregate functions — COUNT, SUM, AVG, MIN, and MAX — with realistic examples you can actually use in your projects.

What Are Aggregate Functions?

Aggregate functions take a group of rows and return a single value. Instead of seeing one result per row, you get a summary. Think of them as the SQL equivalent of a calculator that works across your entire dataset.

We'll use a sample orders table throughout this article:

CREATE TABLE orders (
    order_id     INT PRIMARY KEY,
    customer_id  INT,
    product_name VARCHAR(100),
    quantity     INT,
    unit_price   DECIMAL(10, 2),
    order_date   DATE,
    status       VARCHAR(20)
);

And some sample data to work with:

INSERT INTO orders (order_id, customer_id, product_name, quantity, unit_price, order_date, status) VALUES
(1,  101, 'Wireless Headphones', 2,  79.99,  '2024-03-01', 'completed'),
(2,  102, 'USB-C Hub',           1,  49.99,  '2024-03-02', 'completed'),
(3,  101, 'Mechanical Keyboard', 1,  129.99, '2024-03-05', 'completed'),
(4,  103, 'Webcam HD',           3,  59.99,  '2024-03-07', 'pending'),
(5,  104, 'Monitor Stand',       1,  39.99,  '2024-03-10', 'completed'),
(6,  102, 'Wireless Mouse',      2,  34.99,  '2024-03-12', 'cancelled'),
(7,  105, 'Laptop Sleeve',       4,  24.99,  '2024-03-15', 'completed'),
(8,  103, 'HDMI Cable',          2,  14.99,  '2024-03-18', 'completed'),
(9,  106, 'Desk Lamp',           1,  44.99,  '2024-03-20', 'pending'),
(10, 101, 'Cable Organizer',     5,   9.99,  '2024-03-22', 'completed');

1. COUNT — How Many Rows Are There?

COUNT answers "how many?" It's arguably the most-used aggregate function in SQL.

Count all rows

SELECT COUNT(*) AS total_orders
FROM orders;

Result:

total_orders
10

Count non-NULL values in a column

COUNT(column_name) skips NULL values, while COUNT(*) counts every row.

SELECT COUNT(order_id) AS orders_with_id
FROM orders;

Count with a filter

How many orders were completed?

SELECT COUNT(*) AS completed_orders
FROM orders
WHERE status = 'completed';

Result:

completed_orders
7

Count distinct values

How many unique customers placed orders?

SELECT COUNT(DISTINCT customer_id) AS unique_customers
FROM orders;

Result:

unique_customers
6

2. SUM — Add It All Up

SUM totals up numeric values. Perfect for revenue calculations, quantity totals, and similar tasks.

Total quantity sold

SELECT SUM(quantity) AS total_items_sold
FROM orders;

Result:

total_items_sold
22

Total revenue from completed orders

Here we calculate revenue by multiplying quantity × unit_price for each row, then summing it all:

SELECT SUM(quantity * unit_price) AS total_revenue
FROM orders
WHERE status = 'completed';

Result:

total_revenue
704.81

Revenue per customer

This is where SUM gets really powerful when combined with GROUP BY:

SELECT 
    customer_id,
    SUM(quantity * unit_price) AS customer_revenue
FROM orders
WHERE status = 'completed'
GROUP BY customer_id
ORDER BY customer_revenue DESC;

Result:

customer_id	customer_revenue
101	369.95
105	99.96
103	29.98
104	39.99
102	49.99

Customer 101 is clearly your best customer!

3. AVG — Find the Average

AVG calculates the mean of a set of values. Great for understanding typical behavior.

Average order value

SELECT AVG(unit_price) AS avg_product_price
FROM orders;

Result:

avg_product_price
48.99

Average items per order

SELECT AVG(quantity) AS avg_items_per_order
FROM orders;

Result:

avg_items_per_order
2.2000

Average revenue per completed order

SELECT ROUND(AVG(quantity * unit_price), 2) AS avg_order_value
FROM orders
WHERE status = 'completed';

Result:

avg_order_value
100.69

Pro tip: AVG ignores NULL values automatically. If a column has NULLs, they won't drag down your average — but be aware of this behavior to avoid surprises.

4. MIN — Find the Smallest Value

MIN returns the lowest value in a column. Works with numbers, dates, and even strings.

Cheapest product

SELECT MIN(unit_price) AS cheapest_product_price
FROM orders;

Result:

cheapest_product_price
9.99

Earliest order date

SELECT MIN(order_date) AS first_order_date
FROM orders;

Result:

first_order_date
2024-03-01

First order per customer

SELECT 
    customer_id,
    MIN(order_date) AS first_order_date
FROM orders
GROUP BY customer_id;

Result:

customer_id	first_order_date
101	2024-03-01
102	2024-03-02
103	2024-03-07
104	2024-03-10
105	2024-03-15
106	2024-03-20

5. MAX — Find the Largest Value

MAX is the mirror image of MIN — it returns the highest value.

Most expensive product

SELECT MAX(unit_price) AS most_expensive_product
FROM orders;

Result:

most_expensive_product
129.99

Most recent order date

SELECT MAX(order_date) AS last_order_date
FROM orders;

Result:

last_order_date
2024-03-22

Biggest single order value per customer

SELECT 
    customer_id,
    MAX(quantity * unit_price) AS biggest_order_value
FROM orders
GROUP BY customer_id
ORDER BY biggest_order_value DESC;

Combining Multiple Aggregates

The real magic happens when you use several aggregate functions together in one query. Here's a full sales summary report:

SELECT 
    COUNT(*)                              AS total_orders,
    COUNT(DISTINCT customer_id)           AS unique_customers,
    SUM(quantity)                         AS total_items_sold,
    ROUND(SUM(quantity * unit_price), 2)  AS gross_revenue,
    ROUND(AVG(quantity * unit_price), 2)  AS avg_order_value,
    MIN(unit_price)                       AS cheapest_item,
    MAX(unit_price)                       AS priciest_item
FROM orders
WHERE status = 'completed';

Result:

total_orders	unique_customers	total_items_sold	gross_revenue	avg_order_value	cheapest_item	priciest_item
7	5	15	704.81	100.69	9.99	129.99

One query, a full business snapshot. That's the power of aggregate functions.

Common Mistakes to Avoid

1. Forgetting to use GROUP BY when selecting non-aggregated columns

This will throw an error in most databases:

-- ❌ Wrong — product_name isn't aggregated or grouped
SELECT product_name, SUM(quantity)
FROM orders;

-- ✅ Correct
SELECT product_name, SUM(quantity) AS total_sold
FROM orders
GROUP BY product_name;

2. Using WHERE instead of HAVING to filter aggregated results

WHERE filters rows before aggregation. HAVING filters after. If you want to filter based on an aggregate result, you need HAVING:

-- ❌ Wrong — can't use SUM() in WHERE
SELECT customer_id, SUM(quantity * unit_price) AS revenue
FROM orders
WHERE SUM(quantity * unit_price) > 100
GROUP BY customer_id;

-- ✅ Correct
SELECT customer_id, SUM(quantity * unit_price) AS revenue
FROM orders
GROUP BY customer_id
HAVING SUM(quantity * unit_price) > 100;

3. Confusing COUNT(*) and COUNT(column)

COUNT(*) counts all rows including NULLs. COUNT(column) skips NULLs in that column. Choose wisely depending on what you actually want to count.

4. Not accounting for NULL in AVG

If some rows have NULL in the column you're averaging, those rows are excluded from both the sum and the count. This can make your average higher than expected.

-- Example: if 3 out of 10 rows have NULL discount, AVG only divides by 7
SELECT AVG(discount_amount) FROM orders;

Key Takeaways

COUNT — Count rows or distinct values. Use COUNT(*) for all rows, COUNT(col) to skip NULLs.
SUM — Add up numeric values. Combine with GROUP BY for per-group totals.
AVG — Calculate the mean. Remember it ignores NULLs.
MIN / MAX — Find the smallest or largest value. Works on numbers, dates, and strings.
Combine multiple aggregates in a single query for powerful summary reports.
Use HAVING (not WHERE) to filter results based on aggregate values.

These five functions are the foundation of nearly every analytical SQL query you'll ever write. Master them, and you'll be answering data questions that used to take spreadsheets and manual work — all with a few clean lines of SQL.

Have a question about aggregate functions, or a favorite use case I didn't cover? Drop it in the comments — I'd love to hear how you're using these in your own projects!

SQL WHERE Clause: The Complete Guide to Filtering Data

Vivek Kumar — Thu, 09 Apr 2026 18:07:05 +0000

SQL WHERE Clause: The Complete Guide to Filtering Data

Imagine you have a database with a million customers, but you only need the ones in New York who signed up last year. Without the WHERE clause, you'd be drowning in data you don't care about. With it, you can slice through your data with surgical precision.

The WHERE clause is one of the first things every SQL developer learns — and one of the most powerful tools you'll use every single day. In this guide, we'll go beyond the basics and cover everything you need to filter data confidently.

Setup: Our Sample Tables

We'll use two tables throughout this guide:

CREATE TABLE customers (
    customer_id   INT PRIMARY KEY,
    first_name    VARCHAR(50),
    last_name     VARCHAR(50),
    email         VARCHAR(100),
    city          VARCHAR(50),
    age           INT,
    signup_date   DATE,
    is_active     BOOLEAN
);

CREATE TABLE orders (
    order_id      INT PRIMARY KEY,
    customer_id   INT,
    product       VARCHAR(100),
    amount        DECIMAL(10,2),
    order_date    DATE,
    status        VARCHAR(20)
);

INSERT INTO customers VALUES
(1,  'Alice',   'Johnson', 'alice@email.com',   'New York',    32, '2022-03-15', true),
(2,  'Bob',     'Smith',   'bob@email.com',     'Chicago',     27, '2023-07-01', true),
(3,  'Carol',   'Davis',   'carol@email.com',   'New York',    45, '2021-11-20', false),
(4,  'David',   'Wilson',  'david@email.com',   'Los Angeles', 31, '2023-01-10', true),
(5,  'Eva',     'Martinez','eva@email.com',     'Chicago',     29, '2022-09-05', true),
(6,  'Frank',   'Brown',   'frank@email.com',   'New York',    52, '2020-06-12', false),
(7,  'Grace',   'Lee',     'grace@email.com',   'Houston',     24, '2023-11-03', true),
(8,  'Henry',   'Taylor',  'henry@email.com',   'Los Angeles', 38, '2022-04-22', true);

Basic Comparisons

The WHERE clause uses comparison operators to filter rows. Here are all the ones you'll regularly use:

Operator	Meaning
`=`	Equal to
`<>` or `!=`	Not equal to
`<`	Less than
`>`	Greater than
`<=`	Less than or equal to
`>=`	Greater than or equal to

Examples

Find all active customers:

SELECT first_name, last_name, city
FROM customers
WHERE is_active = true;

Result: Alice, Bob, David, Eva, Grace, Henry

Find customers aged 30 or older:

SELECT first_name, age, city
FROM customers
WHERE age >= 30;

Result:

first_name	age	city
Alice	32	New York
Carol	45	New York
David	31	Los Angeles
Frank	52	New York
Henry	38	Los Angeles

Combining Conditions: AND, OR, NOT

Real-world queries almost always involve multiple conditions.

AND — both conditions must be true

Active customers in New York:

SELECT first_name, last_name
FROM customers
WHERE city = 'New York'
  AND is_active = true;

Result: Alice Johnson

OR — at least one condition must be true

Customers in New York OR Chicago:

SELECT first_name, city
FROM customers
WHERE city = 'New York'
   OR city = 'Chicago';

Result: Alice (New York), Bob (Chicago), Carol (New York), Eva (Chicago), Frank (New York)

NOT — negate a condition

Customers NOT from New York:

SELECT first_name, city
FROM customers
WHERE NOT city = 'New York';

Combining AND and OR (use parentheses!)

Find active customers who are either from New York or under 30:

-- Without parentheses — this may not do what you expect!
SELECT first_name, age, city
FROM customers
WHERE is_active = true AND city = 'New York' OR age < 30;

-- With parentheses — clear and correct
SELECT first_name, age, city
FROM customers
WHERE is_active = true
  AND (city = 'New York' OR age < 30);

The second version is explicit: it finds active customers where either city is New York or age is under 30. AND binds tighter than OR in SQL, just like multiplication binds tighter than addition in math — parentheses make your intent unmistakable.

IN — Match Any Value in a List

Instead of chaining multiple OR conditions, use IN:

-- Verbose version
SELECT first_name, city
FROM customers
WHERE city = 'New York' OR city = 'Chicago' OR city = 'Houston';

-- Clean version with IN
SELECT first_name, city
FROM customers
WHERE city IN ('New York', 'Chicago', 'Houston');

Both return the same result, but IN is much cleaner. You can also negate it:

-- Customers NOT from these cities
SELECT first_name, city
FROM customers
WHERE city NOT IN ('New York', 'Chicago');

Result: David (Los Angeles), Grace (Houston), Henry (Los Angeles)

BETWEEN — Range Filtering

BETWEEN checks if a value falls within an inclusive range. Works with numbers, dates, and strings.

Numeric range

Customers aged 25–35:

SELECT first_name, age
FROM customers
WHERE age BETWEEN 25 AND 35;

Result: Alice (32), Bob (27), David (31), Eva (29)

Note: BETWEEN x AND y is inclusive on both ends — equivalent to >= x AND <= y.

Date range

Customers who signed up in 2022:

SELECT first_name, signup_date
FROM customers
WHERE signup_date BETWEEN '2022-01-01' AND '2022-12-31';

Result: Alice (2022-03-15), Eva (2022-09-05), Henry (2022-04-22)

IS NULL / IS NOT NULL

NULL in SQL means "unknown" or "missing data." You can't use = NULL to check for it — you must use IS NULL:

-- This NEVER works (always returns 0 rows)
SELECT * FROM customers WHERE email = NULL;

-- This is correct
SELECT * FROM customers WHERE email IS NULL;

Let's add a customer with a missing email to demonstrate:

INSERT INTO customers VALUES
(9, 'Ian', 'Clark', NULL, 'Boston', 33, '2023-05-10', true);

-- Find customers with no email on file
SELECT first_name, last_name
FROM customers
WHERE email IS NULL;

Result: Ian Clark

-- Find customers who DO have an email
SELECT COUNT(*) AS customers_with_email
FROM customers
WHERE email IS NOT NULL;

LIKE — Pattern Matching

LIKE lets you search for patterns in text using two wildcards:

% — matches any sequence of characters (including none)
_ — matches exactly one character

Find emails from Gmail:

SELECT first_name, email
FROM customers
WHERE email LIKE '%@gmail.com';

Find customers whose first name starts with a vowel (A or E):

SELECT first_name
FROM customers
WHERE first_name LIKE 'A%'
   OR first_name LIKE 'E%';

Result: Alice, Eva

Find customers with a 5-letter first name:

SELECT first_name
FROM customers
WHERE first_name LIKE '_____';

Result: Alice, Grace, Henry, Frank (5 underscores = 5 characters)

Case sensitivity: LIKE is case-sensitive in PostgreSQL but case-insensitive in MySQL. In PostgreSQL, use ILIKE for case-insensitive matching.

Putting It All Together

Here's a real-world query combining multiple WHERE techniques — find active customers aged 25–40 who live in New York or Chicago, and whose email address we have on file:

SELECT 
    first_name,
    last_name,
    age,
    city,
    email
FROM customers
WHERE is_active = true
  AND age BETWEEN 25 AND 40
  AND city IN ('New York', 'Chicago')
  AND email IS NOT NULL
ORDER BY age ASC;

Result:

first_name	last_name	age	city	email
Bob	Smith	27	Chicago	bob@email.com
Eva	Martinez	29	Chicago	eva@email.com
Alice	Johnson	32	New York	alice@email.com

One clean query, perfectly targeted results.

Common Mistakes to Avoid

1. Using = NULL instead of IS NULL

-- ❌ Always returns zero rows
WHERE discount_code = NULL

-- ✅ Correct
WHERE discount_code IS NULL

2. Forgetting parentheses with AND/OR

-- ❌ Might not mean what you think
WHERE status = 'active' AND city = 'NY' OR city = 'LA'

-- ✅ Explicit and correct
WHERE status = 'active' AND (city = 'NY' OR city = 'LA')

3. Doing math on the filtered column (kills index performance)

-- ❌ Forces a full table scan — can't use an index on order_date
WHERE YEAR(order_date) = 2023

-- ✅ Uses index on order_date
WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31'

4. Using LIKE with a leading wildcard (also kills index performance)

-- ❌ Can't use index — scans every row
WHERE email LIKE '%@gmail.com'

-- ✅ Can use index — only scans rows starting with 'alice'
WHERE email LIKE 'alice%'

Key Takeaways

Use comparison operators (=, <>, <, >, etc.) for basic filtering.
Combine conditions with AND, OR, and NOT — use parentheses when mixing them.
IN is cleaner than multiple OR conditions for matching a list of values.
BETWEEN provides inclusive range filtering for numbers and dates.
Always use IS NULL or IS NOT NULL — never = NULL.
LIKE with % and _ enables pattern matching; avoid leading wildcards on large tables.

The WHERE clause is the foundation of meaningful SQL queries. Mastering it means you can always get exactly the data you need — no more, no less.

Got a tricky filtering scenario you've run into? Drop it in the comments — I'd love to help work through it!