CODEWITHSUNDEEP
mysql
sankar
sankar

Reputation: 191

Pass number as a column name in select statement of Sql

My query is like this

select 5 from mytable_name;

Then the output is like column name 5 and the value is 5 printing as many max number of rows exists in that table. Can anybody tell the reason why this query is working like this?

Upvotes: 3

Views: 7146

Answers (5)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79909

Can anybody tell the reason why this query is working like this?

You are selecting a string literal value '5' for each row in your table:

select 5 from mytable_name;

And this works fine. Because in the SELECT statement you can select:

  • Column reference,
  • Literal value like in your case.
  • Function.
  • value expression.
  • Select expression.

As defined by the standard SQL1:

enter image description here

Update:

However, If you have a column with a name is a number like in your case, you have to escape it in order to select the values in it like so:

 SELECT `143` FROM Table1;

This will select all the rows in the column 143.

But, this:

 SELECT 143 FROM Table1;

Will select the string literal 143 for each row found in the table.

Note that: If possible, try not to name these columns this way, it is recommended and a best practice, not to do this.

SQL Fiddle Demo

Update 2:

Note that, if you select 143 or '143', or even "143" this will select the literal value 143 not the column date. The following are the same:

SELECT 143 FROM Table1;
SELECT '143' FROM Table1;
SELECT "143" FROM Table1;

All these SELECTs won't select the data in the column, They will select the literal value 143 not the column data. See this demo:

Demo

You have to escape the column name with the two:

``

Like this:

SELECT `143` FROM table1;

Not:

SELECT '143' FROM table1'

Like what I did here:

The right Demo

1Image From: SQL Queries for Mere Mortals

Upvotes: 6

Fred Wuerges
Fred Wuerges

Reputation: 1965

With the command select 5 ... you are viewing a fixed value. Same thing you run the following command: select "test", you will be displaying a fixed string.
Using ... from mytable_name you're looking for all record of this table.

With this we can conclude that for each record in the table mytable_name shows you the fixed value "5".

Upvotes: 0

Eric Petroelje
Eric Petroelje

Reputation: 60498

Since you don't have anything in your where clause, it is selecting all the rows from your table. The fact that you don't select any of the columns is irrelevant - you'll still get a result for each row in the table.

Upvotes: 0

JMac
JMac

Reputation: 533

You want 'SELECT * FROM mytable_name LIMIT 0,5' perhaps?

Upvotes: 1

juergen d
juergen d

Reputation: 204746

from mytable

will select all rows from your table if there is no where condition that shrinks that result. and

select 5

will select the constant number 5 for every record. If you use a column name in the select part then that value will be selected for every record.

The DB engine will name the result 5 because it automatically generates a column name and 5 is the logical name for that.

Upvotes: 1

Related Questions