How to rename columns with `SELECT`?

Question

I have two tables with one identical column name, but different data. I want to join the tables, but access both columns (row["price"], row["other_price"]): How can I rename/alias one of them in the select statement? (I do not want to rename them in the DB)

Jesher

Answer 1

SELECT table1.price, table2.price AS other_price ...

Ruche

How about 25 identical column in two tables? should I go through writing AS statement for all 25 columns? – Selwin

short answer yes. OTOH if you don't have enough time to write the 25 AS statements, you could pull the column names from SysColumns and write some SQL to create SQL. The simple way is maybe 5 minutes ... – Ruche

My SQL dialect does not allow me to use strings as the column name. SELECT table1.price, table2.price AS other_price worked for me. – Chitkara

In my case single quotes after AS won't work. Needs to be without quotes. – Cobham

What if the table has many columns, but only one has a conflicting name, and you want to select all columns (like for a view using JOIN): Is there a way to avoid listing all the column names (i.e. can I use * combined with AS somehow?)? – Undenominational

If you're using SSMS you can select the columns in the object explorer and drag that into the query pane, you get a full list of all the columns that you can then edit. – Ruche

Answer 2

select t1.Column as Price, t2.Column as Other_Price
from table1 as t1 INNER JOIN table2 as t2 
ON t1.Key = t2.Key 

like this ?

Ess

Answer 3

Use the AS keyword:

select a.Price as PriceOne, b.price as PriceTwo 
from tablea a, tableb b

Nucleolated

Answer 4

If, like me, you are doing this for a column which then goes through COALESCE / array_to_json / ARRAY_AGG / row_to_json (PostgreSQL) and want to keep the capitals in the column name, double quote the column name, like so:

SELECT a.price AS "myFirstPrice", b.price AS "mySecondPrice"

Without the quotes (and when using those functions), my column names in camelCase would lose the capital letters.

Erich

Answer 5

if you are using sql server, use brackets or single quotes around alias name in a query you have in code.

Scharaga

Answer 6

Also you may omit the AS keyword.
SELECT row1 Price, row2 'Other Price' FROM exampleDB.table1;
in this option readability is a bit degraded but you have desired result.

Preponderate

Answer 7

There is no need to use AS, just use:

SELECT table1.price Table1 Price, table2.price Table2 Price, .....

Kailyard

Answer 8

Another option you can choose:

select price = table1.price , other_price = table2.price from .....

Reference:

In case you are curious about the performance or otherwise of aliasing a column using “=” versus “as”.

Lamellicorn

Answer 9

<For the columns of a table in PostgreSQL>:

You can rename them with or without AS as shown below:

postgres=# SELECT first_name AS fn, last_name AS ln FROM person;
  fn  |  ln
------+-------
 John | Smith
(1 row)

Or:

postgres=# SELECT first_name fn, last_name ln FROM person;
  fn  |  ln
------+-------
 John | Smith
(1 row)

<For the default columns ?column? of raw values in PostgreSQL>:

You can rename them with or without AS as shown below:

postgres=# SELECT 'Apple' AS v1, 'Orange' AS v2, 'Banana';
  v1   |   v2   | ?column?
-------+--------+----------
 Apple | Orange | Banana
(1 row)

Or:

postgres=# SELECT 'Apple' v1, 'Orange' v2, 'Banana';
  v1   |   v2   | ?column?
-------+--------+----------
 Apple | Orange | Banana
(1 row)

And, you can rename them with types as shown below:

postgres=# SELECT TEXT 'Apple', VARCHAR(20) 'Orange', 'Banana';
 text  | varchar | ?column?
-------+---------+----------
 Apple | Orange  | Banana
(1 row)

And, you can use types with or without AS as shown below:

postgres=# SELECT TEXT 'Apple' AS v1, VARCHAR(20) 'Orange' AS v2, 'Banana';
  v1   |   v2   | ?column?
-------+--------+----------
 Apple | Orange | Banana
(1 row)

Or:

postgres=# SELECT TEXT 'Apple' v1, VARCHAR(20) 'Orange' v2, 'Banana';
  v1   |   v2   | ?column?
-------+--------+----------
 Apple | Orange | Banana
(1 row)

Chilson