Oracle SQL - Select Column Aliases

Introduction

By default, the column names of the table are displayed above your query result.

You can specify different result column headings.

You include the heading you want to appear, called a column alias, in the SELECT clause of your query.

select ename, init, msal salary 
from   emp 
where  deptno = 30; 
  

In this example, there is no comma between MSAL and SALARY.

SALARY is used instead of MSAL as a column heading.

ANSI/ISO SQL standard supports the optional keyword AS between any column name and its corresponding column heading (column alias).

Using this keyword enhances readability. You can formulate the query above as follows:

select ename, init, msal AS salary 
from   emp 
where  deptno = 30;  

Demo

SQL>
SQL> drop table emp;

Table dropped.-- ww  w . j  a v a2s.c  om

SQL> create table emp(
  2  empno      NUMBER(4)    primary key,
  3  ename      VARCHAR2(8)  not null   ,
  4  init       VARCHAR2(5)  not null   ,
  5  job        VARCHAR2(8)             ,
  6  mgr        NUMBER(4)               ,
  7  bdate      DATE         not null   ,
  8  msal       NUMBER(6,2)  not null   ,
  9  comm       NUMBER(6,2)             ,
 10  deptno     NUMBER(2)    default 10) ;
SQL>
SQL> insert into emp values(7001,'SMITH','N',  'TRAINER', 7902,date '1975-12-17',  1800 , NULL, 20);
SQL> insert into emp values(7002,'ALLEN','JAM','SALESREP',7006,date '1971-05-20',  1600, 300,   30);
SQL> insert into emp values(7003,'WARD', 'TF' ,'SALESREP',7006,date '1972-03-02',  1250, 500,   10);
SQL> insert into emp values(7004,'JACK', 'JM', 'MANAGER', 7009,date '1977-04-02',  2975, NULL,  20);
SQL> insert into emp values(7005,'BROWN','P',  'SALESREP',7006,date '1976-09-28',  1250, 1400,  30);
SQL> insert into emp values(7006,'BLAKE','R',  'MANAGER', 7009,date '1973-11-01',  2850, NULL,  10);
SQL> insert into emp values(7007,'CLARK','AB', 'MANAGER', 7009,date '1975-06-09',  2450, NULL,  10);
SQL> insert into emp values(7008,'SCOTT','DEF','TRAINER', 7004,date '1979-11-26',  3000, NULL,  20);
SQL> insert into emp values(7009,'KING', 'CC', 'DIRECTOR',NULL,date '1972-10-17',  5000, NULL,  10);
SQL> insert into emp values(7010,'BREAD','JJ', 'SALESREP',7006,date '1978-09-28',  1500, 0,     30);
SQL> insert into emp values(7011,'ADAMS','AA', 'TRAINER', 7008,date '1976-12-30',  1100, NULL,  20);
SQL> insert into emp values(7012,'JONES','R',  'ADMIN',   7006,date '1979-10-03',  8000, NULL,  30);
SQL> insert into emp values(7902,'FORD', 'MG', 'TRAINER', 7004,date '1979-02-13',  3000, NULL,  20);
SQL> insert into emp values(7934,'MARY', 'ABC','ADMIN',   7007,date '1972-01-23',  1300, NULL,  10);
SQL> --No alias
SQL> select ename, init, msal
  2  from   emp
  3  where  deptno = 30;

ENAME    | INIT  |      MSAL
-------- | ----- | ---------
ALLEN    | JAM   |  01600.00
BROWN    | P     |  01250.00
BREAD    | JJ    |  01500.00
JONES    | R     |  08000.00

SQL>
SQL>
SQL> select ename, init, msal salary
  2  from   emp
  3  where  deptno = 30;

ENAME    | INIT  |    SALARY
-------- | ----- | ---------
ALLEN    | JAM   |  01600.00
BROWN    | P     |  01250.00
BREAD    | JJ    |  01500.00
JONES    | R     |  08000.00

SQL>
SQL>
SQL> select ename, init, msal AS salary
  2  from   emp
  3  where  deptno = 30;

ENAME    | INIT  |    SALARY
-------- | ----- | ---------
ALLEN    | JAM   |  01600.00
BROWN    | P     |  01250.00
BREAD    | JJ    |  01500.00
JONES    | R     |  08000.00

SQL>
SQL>

• Previous
• Next