 |
| SQL Aliases in DB2 for i SQL |
SQL Aliases are the temporary name given to a table or its column while executing any SQL query. This makes us to provide more meaningful name for the columns. AS keyword is used to give temporary name.
Syntax:
Alias on Column
SELECT column_name AS alias_name FROM table_name
Alias on Table
SELECT column_name(s) FROM table_name AS alias_name
Example to give Columns name Temporary name
SELECT CUSTID as Customer_Id , CUSTNAME as Customer_Name , CUSTCITY as City , CUSTSTATE as State , CUSTCOUNTRY as Country FROM customer
Table Data
CUSTOMER_ID CUSTOMER_NAME CITY STATE COUNTRY
1 Amit PATNA BIHAR INDIA
2 Anil PATNA BIHAR INDIA
3 Dhanraj HISAR HARYANA INDIA
4 Udeep KOCHI KERELA INDIA
5 Yatin IDUKKI KERALA INDIA
6 Tinku AGRA UTTAR PRADESH INDIA
7 Mounish KOLKATA WEST BENGAL INDIA
8 Annamalai CHENNAI TAMIL NADU INDIA
9 Kannan CHENNAI TAMIL NADU INDIA
10 TE_ST NOIDA UTTARPRADESH INDIA
11 T%EST NOIDA UTTARPRADESH INDIA
Keyword AS is optional
AS keywowrd is optional to use. Therefore, the above query can be written as below to give column temporary names.
SELECT CUSTID Customer_Id , CUSTNAME Customer_Name , CUSTCITY City , CUSTSTATE State , CUSTCOUNTRY Country FROM customer
Example to give table name Temporary name
SELECT c.custid FROM customer C WHERE c.CUSTID = 1
Here, C is the alias name for the table and then we can differentiate the column name or point to the column name of that column in my SQL query as above.
Using Aliases With Space Character
If we want that alias names contain one or more spaces like "Customer Last Name", then we need to sorround the alias name with the DOUBLE QUOTES.
Using Double Quotes "" for aliases with space characters
SELECT CUSTID as "Customer Id" , CUSTNAME as "Customer Name" , CUSTCITY as "City Of State" , CUSTSTATE as "State Of Country" , CUSTCOUNTRY as "Country in the World" FROM customer
Table data
"Customer Id" "Customer Name" "City Of State" "State Of Country" "Country in the Worl
d"
1 Amit PATNA BIHAR INDIA
2 Anil PATNA BIHAR INDIA
3 Dhanraj HISAR HARYANA INDIA
4 Udeep KOCHI KERELA INDIA
5 Yatin IDUKKI KERALA INDIA
6 Tinku AGRA UTTAR PRADESH INDIA
7 Mounish KOLKATA WEST BENGAL INDIA
8 Annamalai CHENNAI TAMIL NADU INDIA
9 Kannan CHENNAI TAMIL NADU INDIA
10 TE_ST NOIDA UTTARPRADESH INDIA
11 T%EST NOIDA UTTARPRADESH INDIA
Example to Concatenate columns and give it an Alias Name
SELECT CustId , CUSTNAME , CUSTCITY concat '-' concat CUSTSTATE concat '-' concat CUSTCOUNTRY as Address FROM customer
Table Data
CUSTID CUSTNAME ADDRESS
1 Amit PATNA-BIHAR-INDIA
2 Anil PATNA-BIHAR-INDIA
3 Dhanraj HISAR-HARYANA-INDIA
4 Udeep KOCHI-KERELA-INDIA
5 Yatin IDUKKI-KERALA-INDIA
6 Tinku AGRA-UTTAR PRADESH-INDIA
7 Mounish KOLKATA-WEST BENGAL-INDIA
8 Annamalai CHENNAI-TAMIL NADU-INDIA
9 Kannan CHENNAI-TAMIL NADU-INDIA
10 TE_ST NOIDA-UTTARPRADESH-INDIA
11 T%EST NOIDA-UTTARPRADESH-INDIA