What changes when a SQL identifier is written in double quotes instead of bare?
answer
- bare names are not case-sensitive
- the engine folds unquoted names
- quotes mean take it exactly as written
- reserved words and spaces need delimiters
- folding direction differs between engines
basics
~20 sA double-quoted (delimited) identifier is taken literally: case-sensitive, and free to contain spaces or reserved words. An unquoted identifier is case-insensitive because the engine folds its case, so quoting can make Orders and orders different objects.
solid answer
~50 sAn unquoted **regular identifier** must look like a plain word — letters, digits, underscores, not a reserved word — and the engine folds its case, which is why `orders`, `Orders` and `ORDERS` all mean the same object. A **delimited identifier** in double quotes is taken exactly as written: it is case-sensitive, and it may contain spaces, punctuation or reserved words, which is how you get a report heading like `AS "Total (USD)"`. The folding direction is dialect-specific: the standard and Oracle fold to upper case, PostgreSQL folds to lower case, so `CREATE TABLE "Orders"` in PostgreSQL creates a table that plain `SELECT * FROM Orders` can no longer find. MySQL uses backticks by default and only treats double quotes as identifier delimiters under the `ANSI_QUOTES` SQL mode; SQL Server also accepts square brackets. Single quotes always delimit string literals, never identifiers.
code
sql · 5 lines-- PostgreSQL: the quoted name is stored exactly, a bare name is folded to lower case
CREATE TABLE "Orders" (id integer);
SELECT * FROM Orders; -- error: relation "orders" does not exist
SELECT * FROM "Orders"; -- worksgo deeper
Know that unquoted table and column names are case-insensitive, that double quotes are for identifiers and single quotes for string literals, and that mixing the two produces confusing errors.
Explain case folding: unquoted names are folded by the engine while quoted names are kept exactly, so a quoted mixed-case name must be quoted at every later reference. Know that the folding direction differs by dialect.
Show the operational judgment: keep schema names lower-case and unquoted so queries port cleanly and tooling stays simple, and confine delimited identifiers to human-facing output aliases in reporting queries.
Own the naming convention across teams and generated schemas. An ORM or migration tool that quotes camel-case names commits every future query and every operator to exact quoting, which is a portability and on-call cost worth deciding once, deliberately.
## Two kinds of identifier SQL distinguishes **regular identifiers** (written bare) from **delimited identifiers** (written inside double quotes). A regular identifier must start with a letter and contain only letters, digits and underscores, and it may not be a reserved word. Crucially, the engine **folds its case** before storing or comparing it, so the name is effectively case-insensitive: `orders`, `Orders` and `ORDERS` denote the same object. A delimited identifier is taken exactly as written. It is case-sensitive, and it may contain anything — spaces, punctuation, reserved words, mixed case: ```sql SELECT o.total AS "Total (USD)" FROM orders AS o; ``` To put a double quote inside a delimited identifier, double it: `"He said ""hi"""`. ## Case folding, and why it bites The standard folds regular identifiers to **upper** case; Oracle and DB2 follow it. PostgreSQL folds to **lower** case, a documented deviation. Either way, the consequence is the same: a delimited identifier whose spelling does not match the folded form becomes reachable only through quotes. ```sql -- PostgreSQL CREATE TABLE "Orders" (id integer); SELECT * FROM Orders; -- error: relation "orders" does not exist SELECT * FROM "Orders"; -- works ``` The first `SELECT` folds `Orders` to `orders`, which is not the name that was created. This is the single most common quoting bug, and it usually arrives via a tool or ORM that quotes every generated name and preserves the original camel case, after which every hand-written query has to quote too. ## Which delimiter, in which dialect - Standard SQL: double quotes for identifiers, single quotes for string literals. - PostgreSQL: double quotes, folding unquoted names to lower case. - MySQL: backticks by default; double quotes act as identifier delimiters only under the `ANSI_QUOTES` SQL mode, and otherwise introduce a string literal. - SQL Server: square brackets `[Total (USD)]`, and double quotes when `QUOTED_IDENTIFIER` is on. That divergence is why `"x"` is not a portable way to write a string, and why copying a MySQL query with backticks into another engine fails immediately. ## Single quotes are not an alternative Single quotes delimit **string literals**. Some engines will accept a string literal where a column alias is expected, but standard SQL wants an identifier there, so portable code writes `AS "Total (USD)"`, never `AS 'Total (USD)'`. Getting this backwards produces a particularly confusing bug: `WHERE status = "ACTIVE"` in an engine that treats double quotes as identifiers is not comparing to a string at all — it is comparing the column to another column named `ACTIVE`, which fails or, if such a column exists, silently compares the wrong things. ## Reserved words Delimiting is the escape hatch for a name that collides with the language: a column called `order`, `user`, `end` or `select` is legal as `"order"`. It is legal, and it is a smell — the name then has to be quoted at every reference forever, in every query, every migration and every ad-hoc investigation. Renaming to `order_line` or `app_user` costs one migration and removes the tax permanently. ## Practical guidance For schema objects, use lower-case `snake_case` names that never need quoting; then the folding direction of your engine simply stops mattering, and queries port cleanly. Reserve delimited identifiers for the place they genuinely earn their keep: **presentation aliases** in reporting queries, where the output column name is a human-facing heading with spaces or units. ## What interviewers listen for A solid answer separates three ideas that candidates commonly merge: case folding (unquoted names are folded, quoted names are not), character set (quoting permits spaces and reserved words), and delimiter choice (double quotes for identifiers, single quotes for strings, with backticks and brackets as vendor variants). The strong candidate also volunteers the advice: avoid quoted schema names, use quoted aliases for report headings.
- Why do experienced teams avoid quoted identifiers for schema objects?Because the quoting becomes permanent. Once a table is created as `"Orders"`, every query, migration, script and ad-hoc investigation must repeat the exact quoting; forget it once and the name folds to a different one and fails. Lower-case snake_case names never need quoting and behave identically across engines.
- How do you include a double quote character inside a delimited identifier?Double it, the same way string literals escape a single quote: `"He said ""hi"""` is an identifier containing `He said "hi"`. It is legal and almost always a sign the name should be changed rather than escaped.
- What goes wrong if you write WHERE status = "ACTIVE" in an engine that follows the standard?Double quotes make `ACTIVE` an identifier, so the predicate compares the `status` column with a column named `ACTIVE` — an unknown-column error, or worse a silent comparison against the wrong column if one exists. String literals always take single quotes.
saying these in an interview costs you the question
- Thinks SQL identifiers are case-sensitive by default
- Says single and double quotes are interchangeable
- Believes quoting is purely cosmetic
- Assumes a bare name matches a quoted name of different case
- Claims every engine delimits identifiers with double quotes