Skip to content

Ambiguous column reference in join

Quick summary

Your join query references a column that exists in both joined tables without specifying which table — the database cannot resolve it.

Why this happens

  • A column like id or name exists in both tables of a join and is used without a table prefix

  • An alias was not used to disambiguate columns with the same name in different tables

  • A USING clause merged columns but the query still references them ambiguously

Minimal example

✗ Broken code
await db.query('SELECT id, name FROM users JOIN orders ON users.id = orders.user_id');
// Error: column id is ambiguous — exists in both users and orders
✓ Fixed code
await db.query('SELECT users.id AS user_id, orders.id AS order_id, users.name FROM users JOIN orders ON users.id = orders.user_id');

Adding table prefixes and AS aliases disambiguates the column references and gives unique names in the result.

How to diagnose

  • Read the error message for the ambiguous column name

  • Check which tables in the join have a column with the same name

  • Add table prefixes or aliases to identify which table the column comes from

How to fix

  • Add a table prefix to the column: SELECT users.id, orders.id FROM users JOIN orders

  • Use table aliases for clarity: SELECT u.id, o.id FROM users u JOIN orders o

  • Rename columns in the output with AS to avoid confusion: SELECT u.id AS user_id, o.id AS order_id

How to prevent

  • Always use table prefixes or aliases for columns in join queries, especially for common names like id

  • Use AS to give unique names to output columns that share names across tables

Related Resources

Related Glossary

Related Lessons

← Back to language