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.

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.

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.

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!