11

I have a table named 'test'

It contains a column named 'AND'

For that I use the following queries

insert into test values ('a','abc');
insert into test values ('b','def');

Now I want to select my 'AND' column from the 'test' table

The query select AND from test; wont work because AND is keyword

I tried 

select "AND" from test;
select 'AND' from test;

Both queries return the wrong results as show below

enter image description here

How can I select my 'AND' column?

Dharman
  • 30,962
  • 25
  • 85
  • 135
Fathah Rehman P
  • 8,401
  • 4
  • 40
  • 42
  • 2
    Hmm, am I the only one who finds it strange that OP didn't make the connection between the backticks in the `create table` and those that are needed in the `select`? :-) And, for the record, `and` is a rather poor name for a column, not because it's a keyword, but because it's not really descriptive. – paxdiablo Sep 13 '12 at 06:40
  • Yes, Its strongly recommended for avoiding to use the reserved key word . – JDGuide Sep 13 '12 at 06:45
  • I know its better to avoid such column names.I dont want to use such column names. I just want to know how to get it. I used ' symbol instead of ` thats why i got wrong results. Anyway In sql server if you use select "name" from aa; then you will get contents in the name column. But in mysql if you use " or ' in column names you wont get correct result. – Fathah Rehman P Sep 13 '12 at 14:42

6 Answers6

30

use backtick to escape keyword instead. just like how you created your table. using double quote or single quote will parse it into string that is why you're getting two records of and

select `AND` from test;
John Woo
  • 258,903
  • 69
  • 498
  • 492
7

  1. Don't create columns with a name that is a keyword.

  2. If you do create a column with such a name, use backticks (MySQL only) or double quotes (SQL standard) to enclose the name. I'm not certain whether you need to switch on some mechanism to have double quotes work in MySQL. This creates a 'delimited identifier'.

    SELECT `AND` AS a, "AND" AS b
  FROM test;

See also:

Community
  • 1
  • 1
Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
  • Let's say we want to use AND (or another reserved word) to the right of AS? Backticks aren't working. *update* Using double quotes around the AS:ed name works. – Jonny Jun 17 '20 at 16:02
2

Its a SO old question and solved. Read here more.

But still you can Try this :

select your_tablename.column_name from table_name;

Your code :-

select test.AND from test;
Community
  • 1
  • 1
JDGuide
  • 6,239
  • 12
  • 46
  • 64
1

If you want to use reserved word as column, you must use backtick around the reserved word column.....

In your case, you have to do like this:

select `AND' from test;
Akash KC
  • 16,057
  • 6
  • 39
  • 59
1

From your question:

CREATE TABLE `test` (
    `count` VARCHAR(50) NULL DEFAULT NULL,
    `AND` VARCHAR(50) NULL DEFAULT NULL
----^---^ Why do you use `backtick` (`) in CREATE TABLE statement?
)

Same way you need to use backtick in SELECT statement.

SELECT `AND` FROM test;
-------^---^-----------

See MySQL: Reserved Words

Himanshu
  • 31,810
  • 31
  • 111
  • 133
0

If you use select setement using "" it select a given sting so you have to give a column name like in mssql you have to use

select [AND] from test;

or in mysql syntex select AND from test;

Anant Dabhi
  • 10,864
  • 3
  • 31
  • 49
  • 1
    No, no, no, a thousand times, no. This will choke your SQL analyser, unless it's incredibly intelligent and can figure out what that and means based on context (none I know of can do this). – paxdiablo Sep 13 '12 at 06:45
  • yup you are right its my mistake i edited this i just forgot and reserved keyword – Anant Dabhi Sep 13 '12 at 06:55