Using backticks around field names

Using backticks permits you to use alternative characters. In query writing it's not such a problem, but if one assumes you can just use backticks, I would assume it lets you get away with ridiculous stuff like

SELECT `id`, `my name`, `another field` , `field,with,comma` 

Which does of course generate badly named tables.

If you're just being concise I don't see a problem with it, you'll note if you run your query as such

EXPLAIN EXTENDED Select foo,bar,baz 

The generated warning that comes back will have back-ticks and fully qualified table names. So if you're using query generation features and automated re-writing of queries, backticks would make anything parsing your code less confused.

I think however, instead of mandating whether or not you can use backticks, they should have a standard for names. It solves more 'real' problems.

The only problem with backticks is that they are not ANSI-SQL compliant, e.g. they don't work in SQL Server.

If there is a chance you would have to port your SQL to another database, use double quotes.

To me it makes a lot of sense to use them at all times when dealing with field names.

• Firstly, once you get into the habit, it doesn't hurt to just hit the backtick key.
• Secondly, to me, it makes it easier to see what exactly are the fields in your query, and what are keywords or methods.
• Lastly, it allows you to use whatever field name you wish when designing your table. Sometimes it makes a lot of sense to name a field "key", "order", or "values"... all of which require backticks when referring to them.

Backticks aren't part of standard ANSI SQL. From the mysql manual:

If the ANSI_QUOTES SQL mode is enabled, it is also allowable to quote identifiers within double quotes

So if you use backticks and then decide to move away from MySQL, you have a problem (although you probably have a lot bigger problems as well)

There isn't anything wrong if you keep using MYSQL, except maybe the visual fuziness of the queries. But they do allow the use of reserved keywords or embedded spaces as table and column names. This is a no-no with most database engines and will prevent any migration at a later time.

As for easy reading, many people use caps for SQL keywords, eg.

SELECT some_fied, some_other_field FROM whatever WHERE id IS NULL;

Well, as far as I know, the whole purpose of using backticks is so you can use names that coincide with reserved keywords. So, if the name isn't colliding with a reserved keyword, I don't see any reason to use backticks. But, that's no reason to ban them, either.

The main use of backticks (`) in SQL is to use them in situations where you are going to call them again in upcoming clauses. In every other time it is recommended to use double quotes("").

For example

SELECT CONCAT(Name, ' in ', city, ', ', statecode) AS `Publisher and Location`,
    COUNT(ISBN) AS "# Books",
    MAX(LENGTH(title)) AS "Longest Title",
    MIN(LENGTH(title)) AS "Shortest Title"
FROM Publisher JOIN Book
ON Publisher.PublisherID = Book.PublisherID WHERE INSTR(name, 'read')>0
GROUP BY `Publisher and Location`
HAVING COUNT(ISBN) > 1;

In the above statement do you see how Publisher and Location is used again in GROUP BY clause.

Instead of using

GROUP BY Name, city, statecode

I just used

GROUP BY Publisher and Location

Only when such situations arise, it is useful to use backticks. In all other times using double quotes is recommended.