setting an alias after the * in sql

Question

Is there a way to create an alias for different columns in ORACLE SQL after the Asterisk (*)?

SELECT * 
FROM TABLE 
WHERE INDEX_1 = 5
AND INDEX_2 = 6

instead is there a way to set an alias after the *?

SELECT * INDEX_1 AS INDECIES
FROM TABLE 
WHERE INDEX_1 = 5
AND INDEX_2 = 6
;

The answer is: No. (If there's only one column, why not type it out?) — jarlh, Dec 19 '17 at 13:23
I just wanted to see if I could change the name of one column while still displaying every other column. I know its not very practical but I am just learning. — Alex Fields, Dec 19 '17 at 13:25
I am not sure if this is what you are looking for, but you can set alias on table Name, resulting on all fields referenced like alias.field — apomene, Dec 19 '17 at 13:26
Since it's a bad habit to leave `SELECT *` in rather than naming all columns explicitly (exceptions: prototyping, `EXISTS()` checks) why would you need this? — Damien_The_Unbeliever, Dec 19 '17 at 13:27
You can duplicate a field and give it an alias, based on your comment of only want to alias ONE field *(as opposed to what your question actually says)*. `SELECT someTable.*, some_field AS INDEX_1 FROM someTable` — MatBailie, Dec 19 '17 at 13:27
`SELECT *` is for lazy ad-hoc queries. Good programming practice is to specify each column. — jarlh, Dec 19 '17 at 13:27

Cee McSharpface · Accepted Answer · 2017-12-19T13:38:51.693

No.

The syntax of the SELECT statement's select_list part in oracle grammar is this:

{ *
  | { query_name.*
      | [ schema. ]
        { table | view | materialized view } .*
      | expr [ [ AS ] c_alias ]
    }
    [, { query_name.*
       | [ schema. ]
         { table | view | materialized view } .*
       | expr [ [ AS ] c_alias ]
       }
    ]...
}

That means, either asterisk and nothing else, or rowsource-qualified asterisk(s) and discrete columns. No c_alias token possible after an *.

What you can do, is to combine asterisks and field lists, even when both parts reference the same table, like here:

SELECT
  TA1.*,
  TA1.Column1 AS ColumnX
FROM Table1 TA1

Using the asterisk is discouraged for various reasons. Use it for convenience in ad-hoc queries.

score 0 · Answer 2 · answered Dec 19 '17 at 13:39

Though, a little bit of "mix" can also be used: a rowsource-qualified asterisk AND (i.e. not OR) a field list, such as

SQL> select d.*, e.ename
  2  from dept d, emp e
  3  where e.deptno = d.deptno
  4    and d.deptno = 10;

    DEPTNO DNAME          LOC           ENAME
---------- -------------- ------------- ----------
        10 ACCOUNTING     NEW YORK      KING
        10 ACCOUNTING     NEW YORK      CLARK
        10 ACCOUNTING     NEW YORK      MILLER
        10 ACCOUNTING     NEW YORK
        10 ACCOUNTING     NEW YORK

SQL>

score 0 · Answer 3 · answered Dec 19 '17 at 14:21

If you are using SQLPLUS you can use the COLUMN command to set the heading displayed for any column regardless of whether it is selected using asterisk or by name.

E.g. COLUMN col_123 HEADING 'Account Number'.

The SQLPLUS reference for this is here : https://docs.oracle.com/cd/B28359_01/server.111/b31189/ch12013.htm

setting an alias after the * in sql

3 Answers3