SQLite中表名与关键字同名导致JOIN失败的解决方法

Hey there! Let's break down and solve these two SQLite problems you're dealing with—they're totally common pitfalls, so you're not alone here.

1. Using a Column That Shares a Name with a SQL Clause/Keyword #

If you've got a column named after a SQL keyword (like WHERE, GROUP, ORDER, or any other reserved term), SQLite will treat it as part of the syntax instead of a column name. To get around this, you need to escape the column name using one of SQLite's supported identifier quotes:

• Use double quotes (this is the SQL standard way):

  SELECT "where", "order" FROM your_table;
  

• Use square brackets (compatible with SQL Server-style syntax):

  SELECT [where], [order] FROM your_table;
  

• Use backticks (compatible with MySQL-style syntax):

  SELECT `where`, `order` FROM your_table;
  

Just wrap the column name in whichever style you prefer, and SQLite will recognize it as a column instead of a SQL clause.

2. Fixing the JOIN Query with the order Table #

Since order is a reserved SQL keyword (used in ORDER BY), SQLite is interpreting it as syntax instead of your table name. The fix is similar to the column issue—escape the table name in your JOIN statement.

Here's how to adjust your original query:

Using double quotes (SQL standard): #

SELECT * FROM employee INNER JOIN "order" ON employee.employeeid = "order".EmployeeId;

Using square brackets: #

SELECT * FROM employee INNER JOIN [order] ON employee.employeeid = [order].EmployeeId;

Using backticks: #

SELECT * FROM employee INNER JOIN `order` ON employee.employeeid = `order`.EmployeeId;

Any of these will tell SQLite to treat order as a table name, not the ORDER BY keyword.

内容的提问来源于stack exchange，提问作者lees0