SQLite: no such column

Result code

SQLITE_ERROR (1)

Product

SQLite library

Versions

SQLite 3.x (examples run on 3.39.2; shell output from 3.50.6)

Category

Missing & Duplicate Objects

Last reviewed

24 October 2026

What it means

A name in the statement is not a column of any table, view or subquery in it (or the prefix before the dot is not a table name or alias in the query), so SQLite rejected the statement before running it.

While preparing a statement, SQLite resolves every bare name against the columns of the tables, views and subqueries in its FROM clause, and every x.name against the table or alias x. A name that matches nothing stops the statement with this error, which repeats the name as you wrote it. Column names are not case-sensitive for ASCII letters, so EMAIL finds email. An INSERT column list reports the missing column differently: table customers has no column named name.

Two SQLite habits change where the error appears. First, SQLite lets WHERE, GROUP BY and HAVING use an alias from the SELECT list (SELECT length(email) AS len ... WHERE len > 5 works on 3.39.2), so the common alias mistake is the opposite one: once a table has an alias, its full name no longer works (FROM customers AS c ... WHERE customers.city fails). Second, the double-quoted string misfeature: when double-quoted strings (DQS) are enabled, a double-quoted word that is not a column is quietly treated as a string literal instead of raising this error.

DQS is a build and connection setting. It was on in PHP's SQLite 3.39.2 and in the Android SDK build of the sqlite3 shell 3.50.6 used here, so SELECT "emial" FROM customers returned the text emial for every row, and WHERE "ciity" = 'Leeds' returned no rows, both without an error. With DQS off (the official sqlite3 shell since 3.41.0, builds compiled with -DSQLITE_DQS=0, or after .dbconfig dqs_dml off in the shell) the same double-quoted value is reported as no such column: "Leeds" - should this be a string literal in single-quotes?. Use single quotes for text and double quotes only for names.

Common causes

• A typo in the column name, or a name from another version of the schema (for example name where the table has full_name).
• The column is added by an ALTER TABLE or migration that has not run on this database file.
• The table has an alias, but the statement uses the full table name (or the wrong alias) in front of the column.
• A subquery or view does not include the column in its SELECT list, so the outer query cannot see it.
• Text written in double quotes with DQS off (SQL Server and MySQL users often do this), or a double-quoted column name that is misspelt with DQS on, which gives wrong results instead of this error.

Diagnose it

Read-only These statements only read the schema (sqlite_schema and read-only PRAGMA queries) or your own data. They do not change data, settings or the database file, so they are safe to run first.

PRAGMA table_info(customers);

SELECT m.name AS table_name, p.name AS column_name
FROM sqlite_schema AS m
JOIN pragma_table_info(m.name) AS p
WHERE m.type IN ('table', 'view') AND p.name LIKE '%mail%'  -- part of the column name
ORDER BY m.name;

SELECT "email" AS quoted_real_column, "emial" AS quoted_typo, typeof("emial") AS typo_type
FROM customers
LIMIT 1;

SELECT sql
FROM sqlite_schema
WHERE name = 'customers';

How to fix it

Each fix is labelled with what it changes. Try read-only checks and the least invasive fix first, and test changes outside production.

SELECT o.id, c.email
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE c.city = 'Leeds';  -- not customers.city

SELECT * FROM customers WHERE city = 'Leeds';  -- not "Leeds"

ALTER TABLE customers ADD COLUMN phone TEXT;

SELECT city, COUNT(*)
FROM (SELECT id, city FROM customers)  -- city added to the inner SELECT
GROUP BY city;

Examples

A misspelt column name

CREATE TABLE customers (id INTEGER PRIMARY KEY, email TEXT, full_name TEXT, city TEXT);

SELECT emial FROM customers;

no such column: emial

Using the table name after giving the table an alias

CREATE TABLE customers (id INTEGER PRIMARY KEY, email TEXT, city TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total REAL);

SELECT o.id, c.email
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE customers.city = 'Leeds';

no such column: customers.city

A column added by a migration that has not run

CREATE TABLE customers (id INTEGER PRIMARY KEY, email TEXT, full_name TEXT, city TEXT);
-- the release expects: ALTER TABLE customers ADD COLUMN phone TEXT;

SELECT email, phone FROM customers;

no such column: phone

Renaming a column that does not exist

CREATE TABLE customers (id INTEGER PRIMARY KEY, email TEXT, full_name TEXT, city TEXT);

ALTER TABLE customers RENAME COLUMN phone TO mobile;

no such column: "phone"

How to prevent it

Use single quotes for every string and keep double quotes for identifiers, so your SQL means the same in every build. Where you control the build or connection, turn double-quoted strings off (compile with -DSQLITE_DQS=0, or .dbconfig dqs_dml off and dqs_ddl off in the shell) so a misspelt quoted name is an error instead of a silent string.

Keep schema changes in versioned migrations that run before the code that needs them, and check the schema in tests (PRAGMA table_info) after migrating. Give every table in a join a short alias and qualify every column with it.

Common questions

Learn more on CodeWithSQL

• SQL WHERE clause explained
• SQL joins guide

Official documentation

• Quirks of SQLite: double-quoted string literals
• ALTER TABLE (ADD, RENAME and DROP COLUMN)
• PRAGMA table_info

Last technically reviewed 24 October 2026; examples and diagnostics run on SQLite 3.39.2 (PHP PDO_SQLITE) and the sqlite3 shell 3.50.6. Messages are the text the SQLite library itself returns (sqlite3_errmsg) for the versions shown.