TSO-ISPF JCL COBOL VSAM DB2 CICS Tools Articles Job Portal Forum Quiz Interview Q&A

DB2 - SQL Aliases


An alias is just that, an alternative name for a field or value.

Aliases are assigned with the AS keyword.

SQL aliases are used to give a table, or a column in a table, a temporary name. Aliases are often used to make column names more readable.

Syntax: Column Alias

SELECT column_name AS alias_name
  FROM table_name;

Syntax: Table Alias

SELECT column_name(s)
  FROM table_name AS alias_name;

DB2 Database:

Below is a selection from the "Product" table in the DB2 database.

ProductIDProductNameCategoryPriceQtyOnHandTotalValue
7001MouseAccessories75.00
7002Harddrive65.00201,300
7003KeyboardAccessories36.00331,118.00
7004RAMComponents23.5016376.00
7005HondaBikes1,200
7006PEN7.451074.50
7007CddriveAccessories75.00
7008SpeakerAccessories75.00
7009AmplifierComponents25.00
7010HeadphoneAccessories100.00


Example 1:

The following SQL statement creates two aliases, one for the ProductID column and one for the ProductName column:

SELECT ProductID AS ID, ProductName AS Product
  FROM Product;

The result of this query is as follows.

IDProduct
7001Mouse
7002Harddrive
7003Keyboard
7004RAM
7005Honda
7006PEN
7007Cddrive
7008Speaker
7009Amplifier
7010Headphone


Example 2:

If the alias name contains spaces, It requires single quotation marks or square brackets.

SELECT
    CONCAT('IBM','MAINFRAMER') AS 'Combined Value'
FROM
    SYSIBM.SYSDUMMY1;

Here is the output:

Combined Value
-------------
IBMMAINFRAMER


Example 3:

The following SQL statement sellects all the products in product table. We use the "Product" table, and give the table aliases as "P" (Here we use aliases to make the SQL shorter):

This is simple example of Table Aliases. we mostly don't use aliases for single table query. Just for an understanding purpose We used aliases for single table query.

SELECT P.ProductID, P.ProductName
FROM Product AS P;

The result of this query is as follows.

ProductIDProductName
7001Mouse
7002Harddrive
7003Keyboard
7004RAM
7005Honda
7006PEN
7007Cddrive
7008Speaker
7009Amplifier
7010Headphone


Aliases can be useful when:

  • There are more than one table involved in a query
  • Functions are used in the query
  • Column names are big or not very readable
  • Two or more columns are combined together


If you have any doubts or queries related to this chapter, get them clarified from our Mainframe experts on ibmmainframer Community!

Are you looking for Job Change? Job Portal