Reserved Words in the SQL Language

Webshop Blog

Posted on 19 August, 2009 in Cheat Sheets

mysql1

When writing SQL queries and naming columns while building your database, you have to be careful which words you use. Some words are reserved by the SQL language for use as special built in commands and functions. Using these words as a column identifier, for example, will throw an SQL error and your query will die. I have found myself many many times pulling my hair out over a particular query, trying to find out why it isn’t working only to later discover that I named one of my columns with a reserved word. Some of these reserved words include SELECT, UPDATE and WHERE. Those you were probably already familiar with if you have ever written any SQL queries; the full list, however, is actually pretty lengthy and contains many commands you may not have known about. Additionally, as you browse this list, take a moment to look up the commands you aren’t familiar with. This can help you learn about some previously unknown and powerful features of the language.

It should be noted, however, that reserved words can actually be used in queries outside of their special uses if you quote them. For example:

SELECT * FROM messages WHERE read=0 LIMIT 10; //bad
SELECT * FROM messages WHERE `read`=0 LIMIT 10; //good

The word “read” is a reserved word, and can only be used as the identifier for a column if it is quoted.

To help you avoid the mistakes I’ve made and stress I’ve suffered. I’ve compiled (what I think to be) a complete list of these reserved words to reference as a sort of cheat sheet in the future when frustrating queries, that you know are correct, just don’t seem to work.

Reserved Words in SQL

`ACCESSIBLE`	`ADD`	`ALL`	`ALTER`
`ANALYZE`	`AND`	`AS`	`ASC`
`ASENSITIVE`	`BEFORE`	`BETWEEN`	`BIGINT`
`BINARY`	`BLOB`	`BOTH`	`BY`
`CALL`	`CASCADE`	`CASE`	`CHANGE`
`CHAR`	`CHARACTER`	`CHECK`	`COLLATE`
`COLUMN`	`CONDITION`	`CONSTRAINT`	`CONTINUE`
`CONVERT`	`CREATE`	`CROSS`	`CURRENT_DATE`
`CURRENT_TIME`	`CURRENT_TIMESTAMP`	`CURRENT_USER`	`CURSOR`
`DATABASE`	`DATABASES`	`DAY_HOUR`	`DAY_MICROSECOND`
`DAY_MINUTE`	`DAY_SECOND`	`DEC`	`DECIMAL`
`DECLARE`	`DEFAULT`	`DELAYED`	`DELETE`
`DESC`	`DESCRIBE`	`DETERMINISTIC`	`DISTINCT`
`DISTINCTROW`	`DIV`	`DOUBLE`	`DROP`
`DUAL`	`EACH`	`ELSE`	`ELSEIF`
`ENCLOSED`	`ESCAPED`	`EXISTS`	`EXIT`
`EXPLAIN`	`FALSE`	`FETCH`	`FLOAT`
`FLOAT4`	`FLOAT8`	`FOR`	`FORCE`
`FOREIGN`	`FROM`	`FULLTEXT`	`GRANT`
`GROUP`	`HAVING`	`HIGH_PRIORITY`	`HOUR_MICROSECOND`
`HOUR_MINUTE`	`HOUR_SECOND`	`IF`	`IGNORE`
`IN`	`INDEX`	`INFILE`	`INNER`
`INOUT`	`INSENSITIVE`	`INSERT`	`INT`
`INT1`	`INT2`	`INT3`	`INT4`
`INT8`	`INTEGER`	`INTERVAL`	`INTO`
`IS`	`ITERATE`	`JOIN`	`KEY`
`KEYS`	`KILL`	`LEADING`	`LEAVE`
`LEFT`	`LIKE`	`LIMIT`	`LINEAR`
`LINES`	`LOAD`	`LOCALTIME`	`LOCALTIMESTAMP`
`LOCK`	`LONG`	`LONGBLOB`	`LONGTEXT`
`LOOP`	`LOW_PRIORITY`	`MASTER_SSL_VERIFY_SERVER_CERT`	`MATCH`
`MEDIUMBLOB`	`MEDIUMINT`	`MEDIUMTEXT`	`MIDDLEINT`
`MINUTE_MICROSECOND`	`MINUTE_SECOND`	`MOD`	`MODIFIES`
`NATURAL`	`NOT`	`NO_WRITE_TO_BINLOG`	`NULL`
`NUMERIC`	`ON`	`OPTIMIZE`	`OPTION`
`OPTIONALLY`	`OR`	`ORDER`	`OUT`
`OUTER`	`OUTFILE`	`PRECISION`	`PRIMARY`
`PROCEDURE`	`PURGE`	`RANGE`	`READ`
`READS`	`READ_WRITE`	`REAL`	`REFERENCES`
`REGEXP`	`RELEASE`	`RENAME`	`REPEAT`
`REPLACE`	`REQUIRE`	`RESTRICT`	`RETURN`
`REVOKE`	`RIGHT`	`RLIKE`	`SCHEMA`
`SCHEMAS`	`SECOND_MICROSECOND`	`SELECT`	`SENSITIVE`
`SEPARATOR`	`SET`	`SHOW`	`SMALLINT`
`SPATIAL`	`SPECIFIC`	`SQL`	`SQLEXCEPTION`
`SQLSTATE`	`SQLWARNING`	`SQL_BIG_RESULT`	`SQL_CALC_FOUND_ROWS`
`SQL_SMALL_RESULT`	`SSL`	`STARTING`	`STRAIGHT_JOIN`
`TABLE`	`TERMINATED`	`THEN`	`TINYBLOB`
`TINYINT`	`TINYTEXT`	`TO`	`TRAILING`
`TRIGGER`	`TRUE`	`UNDO`	`UNION`
`UNIQUE`	`UNLOCK`	`UNSIGNED`	`UPDATE`
`USAGE`	`USE`	`USING`	`UTC_DATE`
`UTC_TIME`	`UTC_TIMESTAMP`	`VALUES`	`VARBINARY`
`VARCHAR`	`VARCHARACTER`	`VARYING`	`WHEN`
`WHERE`	`WHILE`	`WITH`	`WRITE`
`XOR`	`YEAR_MONTH`	`ZEROFILL`

Leave a Response

One Response to “Reserved Words in the SQL Language”

hermes handbags says:

7 July, 2010 at 7:51 pm

When writing SQL queries and naming columns while building your database, you have to be careful which words you use. Some words are reserved by the SQL language for use as special built in commands and functions. Using these words as a column identifier, for example, will throw an SQL error and your query will die. I have found myself many many times pulling my hair out over a particular query, trying to find out why it isn’t working only to later discover that I named one of my columns with a reserved word. Some of these reserved words include SELECT, UPDATE and WHERE. Those you were probably already familiar with if you have ever written any SQL queries; the full list, however, is actually pretty lengthy and contains many commands you may not have known about. Additionally, as you browse this list, take a moment to look up the commands you aren’t familiar with. This can help you learn about some previously unknown and powerful features of the language.

Webshop Blog