# How to Fix MySQL Error 1054: Unknown Column in 'field list'

Source: https://www.bytebase.com/reference/mysql/error/1054-unknown-column-in-field-list/

---

## Error Message

```sql
ERROR 1054 (42S22): Unknown column 'emial' in 'field list'
```

The error also appears with different clause contexts:

```sql
ERROR 1054 (42S22): Unknown column 'status' in 'where clause'
ERROR 1054 (42S22): Unknown column 'u.name' in 'on clause'
ERROR 1054 (42S22): Unknown column 'total' in 'order clause'
```

The clause name in the message tells you where to look — `field list` means SELECT, `where clause` means WHERE, `on clause` means a JOIN condition.

## What Triggers This Error

MySQL 1054 has several distinct causes, and the fix depends on which clause the error points to:

- **Typo in column name** — `emial` instead of `email` (any clause)
- **Column alias used in WHERE** — MySQL doesn't resolve SELECT aliases in WHERE (aliases are allowed in HAVING, GROUP BY, and ORDER BY)
- **Missing table alias in a JOIN** — column exists but MySQL can't resolve which table owns it
- **Reserved word used as column name without backticks** — `order`, `group`, `key`, `status`
- **Wrong column name in ON clause** — JOIN condition references a column that doesn't exist
- **View broken by schema change** — underlying table dropped a column the view references
- **Post-migration mismatch** — column renamed or dropped but queries still use the old name

## Fix by Scenario

### Typo in column name (field list, where clause)

Check what columns actually exist:

```sql
-- Quick check
DESCRIBE users;

-- Full detail
SELECT COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users'
ORDER BY ORDINAL_POSITION;
```

### Column alias used in WHERE

MySQL doesn't let you reference a SELECT alias in WHERE. Unlike HAVING (where MySQL does allow aliases), WHERE is evaluated before SELECT, so aliases are not yet available.

```sql
-- Bad: alias not visible in WHERE
SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM users WHERE full_name LIKE '%Smith%';

-- Good: repeat the expression
SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM users WHERE CONCAT(first_name, ' ', last_name) LIKE '%Smith%';

-- Alternative: wrap in a subquery
SELECT * FROM (
  SELECT *, CONCAT(first_name, ' ', last_name) AS full_name FROM users
) t WHERE full_name LIKE '%Smith%';
```

### Reserved word used as column name

MySQL has a long list of reserved words. If your column happens to be named `order`, `group`, `key`, `rank`, or `status`, you need backticks:

```sql
-- Bad: 'order' is a reserved word
SELECT order FROM purchases;

-- Good: backtick the reserved word
SELECT `order` FROM purchases;
```

The full list is at [MySQL Reserved Words](https://dev.mysql.com/doc/refman/8.0/en/keywords.html). Common offenders: `order`, `group`, `key`, `index`, `rank`, `status`, `condition`.

### Wrong column name in ON clause (JOIN)

When the error says `in 'on clause'`, the problem is in a JOIN condition:

```sql
-- Bad: wrong column name
SELECT * FROM orders o JOIN customers c ON o.cust_id = c.id;

-- Good: use the correct column name
SELECT * FROM orders o JOIN customers c ON o.customer_id = c.id;
```

Always qualify columns with table aliases in JOINs:

```sql
SELECT c.name, o.total
FROM orders o JOIN customers c ON o.customer_id = c.id;
```

### View broken by schema change (SELECT *)

If a view was created with `SELECT *` and the underlying table later dropped a column, queries against the view fail with 1054. Recreate the view:

```sql
-- Check the view definition
SHOW CREATE VIEW my_view;

-- Recreate after schema change
CREATE OR REPLACE VIEW my_view AS
SELECT id, email, name FROM users;  -- explicit columns, not *
```

### ORDER BY with UNION

In a UNION, the result columns are named after the first SELECT. ORDER BY a name that doesn't appear in that first SELECT triggers 1054:

```sql
-- Bad: 'address' is not a column name in the UNION result
-- (result columns are 'id' and 'email', taken from the first SELECT)
SELECT id, email FROM users UNION SELECT id, address FROM contacts ORDER BY address;

-- Good: use the first SELECT's column name, or a positional number
SELECT id, email FROM users UNION SELECT id, address FROM contacts ORDER BY email;
SELECT id, email FROM users UNION SELECT id, address FROM contacts ORDER BY 2;
```

### Post-migration column rename or drop

If a column was recently renamed or dropped:

```sql
-- Check if the old column still exists
SHOW COLUMNS FROM users LIKE 'user_name';

-- See all current columns
SHOW COLUMNS FROM users;
```

In ORM-based apps (Django, Rails, Laravel), the model fields must match the database columns. If you renamed a column in a migration but didn't update the model, or the migration hasn't been applied to this environment, you get 1054.

## Prevention

- Use `DESCRIBE tablename` to verify column names before writing queries
- Avoid `SELECT *` in views — use explicit column lists so schema changes surface immediately
- Backtick any column name that might be a reserved word
- In ORMs, run pending migrations before deploying code that references new or renamed columns
- Use positional ORDER BY (`ORDER BY 2`) in UNION queries instead of column names

> **Note:** Bytebase's [SQL Review](https://www.bytebase.com/docs/sql-review/review-rules/) can catch unknown column references during change review before they reach production. See also [ERROR 1146: Table Doesn't Exist](/reference/mysql/error/1146-table-doesnt-exist) for the related table-not-found error.