0Pricing
SQL Interview Prep · Lesson

Filtering on Calculated Values

Why functions on columns kill index usage and how interviewers probe this.

Filtering on Calculated Values is a free SQL Interview Prep lesson on CoddyKit — lesson 4 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Why This Question Separates Levels

The prompt sounds innocent: this query is correct but slow, why? Often the answer is that the WHERE clause wraps an indexed column in a function. That makes the predicate non-sargable: the optimizer can no longer use the index and must scan every row.

This lesson explains sargability, shows the rewrites interviewers want, and covers where a calculated filter actually belongs.

Sargable in One Definition

Sargable (Search ARGument ABLE) means a predicate can use an index to seek directly to matching rows. The rule of thumb: the indexed column must appear bare on one side of the comparison, not buried inside a function or expression.

  • Sargable: col = 5, col > 100, col LIKE 'abc%'
  • Non-sargable: FUNC(col) = 5, col + 1 > 100

The Function-on-Column Anti-Pattern

Here the goal is orders placed in 2024. Wrapping the column in YEAR() forces the engine to compute the year for every single row before it can compare, so the index on order_date is useless.

It returns the right answer but scans the whole table. On a large table this is the difference between milliseconds and minutes.

-- non-sargable: function on the indexed column
SELECT *
FROM orders
WHERE YEAR(order_date) = 2024;

Rewrite as a Range

The fix is to leave order_date bare and express the condition as a half-open range. Now the index on order_date can seek straight to the start of 2024 and stop at 2025.

Same result, but an index range scan instead of a full scan. This range rewrite is the single most-tested sargability fix in interviews.

-- sargable: column stays bare
SELECT *
FROM orders
WHERE order_date >= '2024-01-01'
  AND order_date <  '2025-01-01';

Arithmetic on the Column

The same problem hides in arithmetic. WHERE salary + bonus > 100000 or WHERE price * 0.9 < 50 both compute on the column and block the index.

Move the math to the constant side wherever possible: rewrite price * 0.9 < 50 as price < 50 / 0.9. The literal is computed once and price stays bare and indexable.

-- before: math on the column (non-sargable)
WHERE price * 0.9 < 50
-- after: math on the constant (sargable)
WHERE price < 50 / 0.9

The Case-Insensitive Search Variant

WHERE LOWER(email) = 'a@b.com' is non-sargable against a plain index on email, because every row's email is lower-cased first.

Two production fixes: store a normalized lower-cased copy and index that, or create a functional index on LOWER(email) so the expression itself is indexed. Naming the functional-index option signals real-world experience.

-- functional index makes the expression sargable
CREATE INDEX idx_email_lower ON users (LOWER(email));
SELECT * FROM users WHERE LOWER(email) = 'a@b.com';

When You Genuinely Need a Calculation

Sometimes the filter truly depends on a computed value with no range rewrite, for example filtering on a ratio. You still cannot reference a SELECT alias in WHERE, because WHERE is evaluated before the SELECT list.

So you either repeat the expression in WHERE or wrap the query in a subquery / CTE and filter the computed column in the outer query.

SELECT *
FROM (
  SELECT *, revenue / NULLIF(visits, 0) AS rev_per_visit
  FROM stats
) t
WHERE t.rev_per_visit > 2.5;

Aggregates Go in HAVING, Not WHERE

A calculation that is an aggregate cannot live in WHERE at all, because WHERE filters individual rows before grouping happens. WHERE SUM(amount) > 1000 is an error.

Aggregate filters belong in HAVING, which runs after GROUP BY. Knowing which clause sees the calculation is itself a frequent execution-order question.

SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 1000;

How Interviewers Probe It

They show a slow query with a function on a column and ask you to make it fast without changing the result. Your move:

  • Identify the function-on-column as non-sargable
  • Rewrite to keep the column bare (range or constant-side math)
  • If no rewrite exists, propose a functional index or a stored computed column

Mentioning EXPLAIN to confirm the plan changed from seq scan to index scan seals the answer.

Trade-Off Awareness

Be balanced: indexes and functional indexes speed reads but slow writes and consume storage. On a tiny table a full scan is fine and adding an index is wasted effort.

The senior answer is conditional: if this column is large and frequently filtered this way, make the predicate sargable or add a functional index; otherwise leave it. Context beats dogma in interviews.

Functional Indexes Make a Calculation Sargable

Sometimes you genuinely must filter on a transformed value — for example a case-insensitive match. Instead of giving up on indexes, create an expression (functional) index on the exact expression you filter by.

  • The optimizer can then use the index even though a function wraps the column.
  • The index expression must match the predicate expression exactly.
-- index the expression you filter on
CREATE INDEX idx_users_lower_email ON users (lower(email));

-- now this predicate stays sargable
SELECT * FROM users WHERE lower(email) = 'amy@example.com';

Quick Check

Identify which predicate the optimizer can index.

Recap

Key takeaways:

  • A predicate is sargable when the indexed column appears bare, not inside a function or arithmetic
  • Rewrite YEAR(col) = 2024 as a half-open range; move math to the constant side
  • For unavoidable expressions use a functional index or a stored computed column
  • You cannot use a SELECT alias in WHERE; aggregates go in HAVING

The classic prompt is a slow query; the classic fix is keeping the column bare.

Frequently asked questions

Is the “Filtering on Calculated Values” lesson free?

Yes — the full text of “Filtering on Calculated Values” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.

What will I learn in “Filtering on Calculated Values”?

Why functions on columns kill index usage and how interviewers probe this. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Interview Prep?

No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 4 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Filtering on Calculated Values” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Interview Prep lesson?

Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. AND/OR Precedence and Parenthesization
  2. BETWEEN, IN, and Inclusive Boundaries
  3. LIKE, Wildcards and Escaping
  4. Filtering on Calculated Values
← Back to SQL Interview Prep