skip to content

Why compare a VARCHAR account_no column to '12345' rather than to the number 12345?

level: juniorimportance: must knowfreq 62%

answer

  1. The two sides are not the same type
  2. Standard SQL calls them not comparable
  3. Someone has to be converted — which one?
  4. A conversion on the column is per-row
  5. Quote the literal, keep the column bare

basics

~20 s

Because the types must match. Comparing a character column to a numeric literal is not a valid comparison in standard SQL: engines either reject it or silently convert every row's stored value, which throws away the index on that column.

solid answer

~50 s

Character and numeric types are not mutually comparable in standard SQL, so `account_no = 12345` is either rejected or quietly repaired by the engine. PostgreSQL rejects it (`operator does not exist: character varying = integer`). Oracle and SQL Server apply data-type precedence, where numeric outranks character, and convert the **column** to a number; MySQL compares both operands as floating-point numbers. Either way a conversion is evaluated for every row, so the predicate is no longer a plain comparison against the stored column value and a plain index on `account_no` cannot be used to seek. Writing `account_no = '12345'` keeps the column bare and the index usable. It also keeps the comparison honest: read as numbers, `'00123'`, `'123'` and `'123 '` all collapse onto 123, which is rarely what a business key is supposed to mean.

code

sql · 11 lines
sql
-- account_no is VARCHAR(20)

-- BAD: numeric literal forces a per-row conversion of the column
SELECT customer_id, balance
FROM   accounts
WHERE  account_no = 12345;

-- GOOD: character literal matches the column's type; index on account_no is usable
SELECT customer_id, balance
FROM   accounts
WHERE  account_no = '12345';

go deeper

for a junior

Remember the rule you can apply every day: the literal's type must match the column's type. Quote strings, leave numbers unquoted, and never assume the database will sort it out for you.

for a middle

Be ready to explain what the engine actually does — reject it, or convert one side — and why converting the column is evaluated once per row and makes the index on that column useless.

for a senior

Expect to be asked how this reaches production undetected: it passes on small dev data, shows only as a scan in the plan, and often arrives via a driver binding rather than hand-written SQL. Show how you would find every instance.

for a principal

The angle to own is prevention: type mismatches are a symptom of columns declared as text because it was convenient. Argue for correct column types and reviewable query conventions rather than case-by-case query firefighting.

## The comparison the standard will not make SQL is a typed language, and the standard only defines comparison between *mutually comparable* types: character strings compare with character strings, numbers with numbers. A predicate that puts a `VARCHAR` column on one side and an integer literal on the other has no defined meaning. Engines therefore take one of two routes — refuse the statement, or silently insert a conversion so that both sides end up in one type. ## What real engines do PostgreSQL is the strict one. `SELECT * FROM accounts WHERE account_no = 12345` on a `varchar` column fails with `ERROR: operator does not exist: character varying = integer`, because no such operator is defined and the untyped-literal machinery cannot rescue a literal that is already unambiguously an integer. MySQL is the permissive one: when a string column is compared with a number, the comparison is performed as floating-point numbers, so both sides are converted to `DOUBLE`. Oracle and SQL Server publish a data-type precedence order in which numeric types outrank character types, so the *character* operand — the column — is converted to the numeric type. In a SQL Server execution plan this shows up as a `CONVERT_IMPLICIT` wrapper around the column in the predicate; in Oracle you see `TO_NUMBER("ACCOUNT_NO")`. The portable lesson: do not rely on any of this. Write the literal in the column's own type. ```sql -- ambiguous at best, a table scan at worst SELECT * FROM accounts WHERE account_no = 12345; -- unambiguous, index-friendly, portable SELECT * FROM accounts WHERE account_no = '12345'; ``` ## Why the index disappears An index on `account_no` is built over the values as stored: character strings, in character order. A predicate that must compute a numeric conversion of `account_no` for every row is asking a question about a *derived* value that the index does not contain. Nor does the conversion preserve order — as text `'9'` sorts after `'12345'`, as numbers 9 sorts before 12345 — so the engine cannot even walk a range of the index and be sure it covered the matches. Its only correct option is to read every row and convert it. On a small development table that costs nothing; on ten million production rows it is the difference between a millisecond and a minute, which is exactly why this defect reaches production with "it worked fine in dev" attached. ## The correctness half of the bug Even when it is fast enough, the numeric reading of a character key is lossy. Identifiers routinely carry meaningful leading zeros (`'00123'`), padding, or non-numeric forms (`'INV-2024-1'`). Converting them to numbers destroys the first two and blows up on the third: an engine that coerces the column can raise a conversion error on a row your query never intended to look at, because SQL does not promise to evaluate one predicate before another. MySQL's floating-point route is worse in a quieter way — a stored `'12345abc'` converts to 12345 with a warning and compares equal to the literal. ## The fix, and the better fix The immediate fix is one character pair: quote the literal so it is a character-string literal, and the column stays bare. In application code the same rule applies to parameters — bind the value with the type that matches the column, not whatever type the value happens to have in your program. The better fix is upstream. If a column is declared `VARCHAR` but genuinely holds nothing but integers, and queries keep comparing it to numbers, change the column's type. One DDL change beats every future query having to defend itself, and it lets the database enforce what the data actually is. ## How to spot it Three signals. First, review: an unquoted numeric literal next to a column you know is character-typed. Second, the plan: any conversion function wrapped around a column inside a predicate — `CONVERT_IMPLICIT`, `TO_NUMBER`, an explicit cast — means the index on that raw column is out of play. Third, the symptom: a full scan on a column that definitely has an index and definitely is selective. Note that the reverse mismatch, a *numeric* column compared to a quoted string, is usually harmless: there the engine converts the single constant, once, and the column stays bare.

  • Is the reverse case, an INTEGER column compared to the quoted literal '42', equally harmful?
    Usually not. There the engine converts a single constant once, and the column stays bare, so an index on it remains usable — PostgreSQL simply resolves the untyped literal as an integer. The habit of matching types is still worth keeping, because a string that is not a valid number turns a harmless comparison into a runtime conversion error.
  • The column is VARCHAR but every value in it is numeric. Should you cast the column in the query or change the schema?
    Change the schema. Casting the column in the query fixes one statement and leaves every future one exposed, and it keeps the index unusable. Altering the column to an integer type makes the comparison natural, lets the database reject non-numeric data, and shrinks both the column and its index. Do it as a migration, after checking every stored value converts.
  • How do you notice this in an execution plan?
    Look at the predicate the plan prints, not just the operator. Any conversion wrapped around a column — CONVERT_IMPLICIT in SQL Server, TO_NUMBER in Oracle, an explicit cast in PostgreSQL — means the plan is filtering on a derived value, so an index on the raw column cannot drive the access. Seeing a full scan on a selective, indexed column is the corroborating symptom.

Looking up a name in a phone book is fast because the book is sorted by name. Asking for "every entry whose name, read as a number, equals 12345" forces you to read the whole book — the ordering you paid for no longer answers the question.

saying these in an interview costs you the question

  • Says the database just converts it, so it does not matter
  • Assumes the literal is converted, never the column
  • Thinks quoting a number is a style preference
  • Claims every engine handles the mismatch the same way
  • Fixes it by casting the column instead of the literal

context

resolving links…