0

1) "select * from `union` WHERE `thanaId`='$thana_id'" // it's working
    VS
2) "select * from union WHERE thanaId='$thana_id'" // it's not working

1 sql query run properly but 2 Sql query can't execute properly. what is meaning of [``]..
what is different between 1 sql query and 2 sql query?

Charlie
  • 22,886
  • 11
  • 59
  • 90
Salman Quader
  • 195
  • 2
  • 13
  • The ` is a name delimiter; since `UNION` is a reserved word, to use it as the name of a field or table, that name must always be delimited. Another example of a similar problem would be if you wanted to `select` a field called `from`, out of a table called `where`. – Uueerdo Apr 22 '16 at 18:25

3 Answers3

2

The backtick encases the string to be treated as a string named literal rather than be interpreted by the MySQL engine. The word union is a reserved / keyword and so MYSQL will be expecting a different syntax because it won't realise that you're using this word as a name identifier.

You would do very well to read and learn this page from the MySQL manual. Every word from the list on this page should be encased in backticks in MySQL queries if you want MySQL to treat it as a name.

It's also useful as much as possible to not name columns/tables with Keywords or reserved words!

Martin
  • 22,212
  • 11
  • 70
  • 132
0

The difference in behavior is because UNION is a Reserved Word in MySQL. It can't be used as an identifier unless it is escaped.

In MySQL, identifiers can be escaped by enclosing them in backtick characters.

The first statement works, because the token following the keyword FROM is interpreted as an identifier, because it's enclosed in backtick characters.

The second statement is throwing syntax error, because the token is interpreted as a reserved word UNION and that appears in a spot where MySQL isn't expecting it (where MySQL doesn't allow it.)

Excerpt from the MySQL Reference manual:

backticks

Identifiers within MySQL SQL statements must be quoted using the backtick character (`) if they contain special characters or reserved words. For example, to refer to a table named FOO#BAR or a column named SELECT, you would specify the identifiers as `FOO#BAR` and `SELECT`. Since the backticks provide an extra level of safety, they are used extensively in program-generated SQL statements, where the identifier names might not be known in advance.

Many other database systems use double quotation marks (") around such special names. For portability, you can enable ANSI_QUOTES mode in MySQL and use double quotation marks instead of backticks to qualify identifier names.

Community
  • 1
  • 1
spencer7593
  • 106,611
  • 15
  • 112
  • 140
  • oh yes.. Thanks @spencer – Salman Quader Apr 22 '16 at 18:32
  • If you want to have a "meaning" associated with the backticks as used in the first statement, the backticks are saying "the characters between us are to interpreted as an *identifier*". That's what they mean. What those backticks allow us to do is to use combinations of characters that are not normally allowed as an identifier. For example, a dash character is not a valid character in an identifier, Also not allowed are combinations of characters that match a reserved word. The backtick characters allow us to use names that violate rules that are normally enforced. – spencer7593 Apr 22 '16 at 18:47
  • Yes I have understood – Salman Quader Apr 22 '16 at 18:52
-1

Difference is that if any word in a mysql statement is quoted with a back tick(`), mysql ASSUMES that you are refering to a table or a column or some mysql field.

For further instructions you may check out this answer

Thanks, RK

Community
  • 1
  • 1
Rehban Khatri
  • 924
  • 1
  • 7
  • 19