1

I have been working on a database for my coursework and have used phpMyAdmin to build it. Now I am working on the queries using the query tool.

When I pick the tables and data I want to query and press "update query" it will generate the query which looks something like this:

SELECT `Customer`.`CustomerName`, `OrderDetails`.`Product`, `OrderDetails`.`QuantityOrdered`
FROM `Customer`
LEFT JOIN `Order` ON `Order`.`Customer` = `Customer`.`CustomerID`
LEFT JOIN `OrderDetails` ON `OrderDetails`.`Order` = `Order`.`OrderID`

This works fine and gives me the results I was expecting. However when I try and write my own query and put something like "SELECT Customer.CustomerName," WITHOUT the ' symbol it won't work and just throws up an error message.

Must I always wrap them using the ' symbol for the query to work?

Mureinik
  • 297,002
  • 52
  • 306
  • 350
xcom
  • 31
  • 3
  • 1
    Possible duplicate of [Using backticks around field names](https://stackoverflow.com/questions/261455/using-backticks-around-field-names) – trincot Jul 05 '18 at 15:40
  • You do not need to escape your identifiers because they are well-formed (non-keywords, no special characters). Most people find it an annoying habit because it makes queries harder to read. Many automatic query generators indulge in this annoying habit. – Gordon Linoff Jul 05 '18 at 15:40
  • Yes if i am correct you have to use the ' symbol when your names are reserved words when writing querys – connorg98 Jul 05 '18 at 15:41
  • You can leave them out unless your names are reserved words. This question has been asked before. – trincot Jul 05 '18 at 15:41
  • Generally if you're asking a question about code that throws an error, including the specific error and a complete example of the code that generated the error in your question will get you better responses. – Don't Panic Jul 05 '18 at 15:44
  • What is the error you get? Actually - you don't need to add the quotes explicitly – BHUVANESH MOHANKUMAR Jul 05 '18 at 15:46

2 Answers2

2

Forward quotes are used to escape object names in MySQL. You don't have to use them unless you use names that wouldn't be valid identifiers - in this case, the table name order is a reserved word, and must be escaped. All the other tables and columns you're using seem to be OK.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Mureinik
  • 297,002
  • 52
  • 306
  • 350
  • Thank you! I have run the query again and just put the ' symbol around the "order" table and it works fine. This is a great help. I am very new to SQL and this website but thank you for taking the time to help. – xcom Jul 05 '18 at 15:55
  • 3
    @xcom Don't use the `'` symbol for quoting identifiers. The straight quote `'` is for quoting strings and dates. The back-tick is not the same symbol. On a US keyboard, back-tick is in the upper left, above the tab key. – Bill Karwin Jul 05 '18 at 15:56
  • Thank you @BillKarwin. I have just been having a look at the reserved word list. Should I avoid any tables names that come up in this list? https://docs.oracle.com/javadb/10.8.3.0/ref/rrefkeywords29722.html – xcom Jul 05 '18 at 15:58
  • 2
    @xcom if you can (e.g., it won't take a huge amount of work to rewrite some old application) - yes – Mureinik Jul 05 '18 at 15:59
  • @xcom You linked to the reserved word list for Derby, not MySQL. Despite SQL being a standard language, all brands add their own "extensions" so you should use the docs for the database you use. Use this list for MySQL reserved words: https://dev.mysql.com/doc/refman/8.0/en/keywords.html – Bill Karwin Jul 05 '18 at 16:00
  • Thanks again for the link @BillKarwin. I am over overwhelmed with the replies and help. I really appreciate it! I will go back and rename my "order" table to something else and check my other table names against that list. Its been a real learning curve doing this SQL course but once it starts to click its a great feeling. – xcom Jul 05 '18 at 16:06
  • @xcom Unlike Gordon, I think using the backticks is good practice. It allows for a more natural database design when you don't have to tiptoe around reserved words lists; and while it is rare, new MySQL releases can and do add new words to those lists. _That said, I don't use them if I am writing throwaway queries, and often omit them if table/field names are so specific that they would never see a reserved words list._ – Uueerdo Jul 05 '18 at 17:12
1

Except for the visual nightmare and ability to create horrendous table names, backticks are entirely unnecessary. You will, however, be required to wrap any variables in single quotes.

As you can see from my example below, using backticks is not a requirement with PHPMYADMIN;

No backticks PHPMYADMIN example The reason it is not working when you remove the backticks is because you have a column called 'order'. Order is a keyword in SQL and therefore cannot be used as a column name without being wrapped in either quotes or backticks.

Mark
  • 691
  • 7
  • 20
  • I tried another query that didn't use my "order" table and it worked fine without the backtick. I think I will change the name of my order table. I appreciate the reply. – xcom Jul 05 '18 at 16:12