sql - Simple SELECT statement fails with "syntax to use near", "ORA-00906", "syntax error at or near" or "syntax near the keyword"

Question

I have a very simple SQL statement

SELECT * FROM Table;

but, my query engine returns a syntax error. Why?

Error Details:

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in >System.Data.dll

Additional information: Incorrect syntax near the keyword 'Table'.

How is this possible? I checked the connection string and it is correct. I checked my table name and it is also correct.

What I am doing wrong?

Answer 1

Okay, Table is a reserved keyword in all variants of SQL.

If you want to call a table Table, and use it in a statement, you have to tell your sql engine that it is an identifier. To do this you need to use Identifier Qualifiers.

for (MS SQL Server) TSQL use square brackets

SELECT * FROM [Table];

for MySQL use `

SELECT * FROM `Table`;

for Oracle and PostgreSQL use quotation marks, these are standards compliant.

SELECT * FROM "Table";

for SQLite you can use any of the above, but quotation marks are prefered.

The Identifier Qualifiers tell the engine that this is an identifier (the name of an object.) Not the name of a keyword, even if they happen to be the same. Without your guidance the query engine can get confused and report an error, or worse, do something unexpected.

Using Identifier Qualifiers is good practice, even if the identifers are not keywords. They better define statements for all parsers, including the fleshy kind.

Naming objects after keywords is generally considered bad practice. So you should try to avoid making identifers the same as keywords. The occasions when a reserved keyword is descriptive of the contents of a table are rare, see the footnote.

e.g. your table is not a Table of tables.

---

The problem and advice is not limited to Tables, Identifiers are required for all database objects inluding Schema, Views and the many types that exist, standard and vendor-specific.

Another form of good practice is to prefix Table indentifiers with a Schema identifier, this helps the query engine a little. When including the Schema identifier, the identifer should be qualified,

for (MS SQL Server) TSQL use square brackets

SELECT * FROM [dbo].[Table];

for MySQL use `

SELECT * FROM `dbo`.`Table`;

for Oracle, PostgreSQL and SQLite use quotation marks

SELECT * FROM "dbo"."Table";

even if your Schema is not named after a keyword, as should be the case.

---

For your reference, to help you avoid conflicts.

A list of TSQL Reserverd Keywords.

A list of MySQl Reserved Keywords.

A list of Oracle Reserved Keywords.

A list of SQLite Reserved Keywords.

A list of PostgreSQL Reserved Keywords.

Notable "gotcha's" include USER and ERROR, which seem to come up when designing systems.

Footnote:

There are occasions when using reseved words for object names may be semantically correct.

Consider the contrived example of an information system for a furniture shop. In this scenario, a table of tables (kitchen, garden, dining, apothecary etc.) may be correct. So, you could argue Table was the correct identifier.

If you always use Identifier Qualifiers, you won't get burned.