26

I need to do a SELECT query where I get the value of the field "money". The field doesn't actually exist in the database. I just need the query to return this field with a fixed value; in this case a value in USD.

How do I return a constant value in a SELECT statement?

Hannah Vernon
  • 70,041
  • 22
  • 171
  • 315
fedejp
  • 263
  • 1
  • 3
  • 4

2 Answers2

32

If the value is always going to be the same you can do something like this:

SELECT 'USD' AS `MONEY`, a.*
  FROM INFORMATION_SCHEMA.SCHEMATA AS a

Just replace a.* with the columns you want to return and INFORMATION_SCHEMA.SCHEMATA with the schema and table(s) you want to query.

I hope this is helpful to you.

Mr.Brownstone
  • 13,102
  • 4
  • 36
  • 54
  • Thanks a lot. Works like a charm. Just curious. What is that last 'AS a' (after 'FROM INFORMATION_SCHEMA.SCHEMATA') for? – fedejp Aug 08 '12 at 18:03
  • No problem at all. The AS specifies an alias for the table, it is essentially a short name that you can reference the table by. – Mr.Brownstone Aug 08 '12 at 18:05
  • 1
    Oh, great. I didn't that you could use aliases for tables. Thanks a lot! – fedejp Aug 08 '12 at 18:08
2

@fedejp you don't have to insert the 'AS'. You can simply write 'INFORMATION_SCHEMA.SCHEMATA a' because the 'AS' is understood.

And if there's only one table in the query (no joins) then you don't need to use an alias and none of the fields specified need 'a.' in front of the field name, as there's no confusion as to which table all the fields are from.

So you can simply write:

SELECT 'USD' AS `MONEY`, * FROM INFORMATION_SCHEMA.SCHEMATA;
GAG and Spice
  • 31
  • 1
  • 1
    Still, aliasing even one table gives you the option to just type 'a.' in the select list (in case of alias 'a') and seeing all the table columns in a dropdown list. I think it's safe to say that pretty much every SQL developer with working experience will always alias their tables - even when just one is selected. The only place where I don't use aliases is when giving a column a subquery result value. Then I would alias the table in the main select, but not alias the one in subquery, so you can quickly distinguish between subquery's fields and main query's fields. – Mashchax Dec 07 '18 at 10:38