Db2 Alias

Summary: in this tutorial, you will learn how to use the Db2 alias including column alias and table alias in the query.

Db2 column alias

When you use the SELECT statement to query data from a table, Db2 use the column names as the headings for the output:

SELECT 
    first_name, 
    last_name
FROM 
    authors
ORDER BY 
    first_name, 
    last_name;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

However, you can change the column headings by giving them aliases using the following syntax:

{column | expression} AS column_alias
Code language: SQL (Structured Query Language) (sql)

The AS keyword is optional.

For example, the following statement uses column aliases to change column headings from first_name to "Author First Name" and last_name to "Author Last Name". Because the column aliases contain spaces, we have to surround it with double quotes (“):

SELECT 
    first_name AS "Author First Name", 
    last_name AS "Author Last Name"
FROM 
    authors
ORDER BY 
    first_name, 
    last_name;
Code language: SQL (Structured Query Language) (sql)

Once column aliases are assigned, they can be referenced in the ORDER BY clause.

This example uses a column alias (full_name) to an expression that concatenates the first name, the middle name, and the last name separated by a space:

SELECT 
    first_name || ' ' || middle_name || ' '  || last_name full_name 
FROM
    authors
ORDER BY 
    full_name;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

Db2 table alias

Similar to a column, you can assign a table an alias using the following syntax:

table_name AS table_alias
Code language: SQL (Structured Query Language) (sql)

The AS keyword in this syntax is also optional.

Once the table is assigned an alias, you can refer its columns using the following syntax:

table_alias.column_name
Code language: SQL (Structured Query Language) (sql)

We often use the table aliases in the queries that involve JOIN clause to make the query shorter.

This query joins two tables: books and authors. Instead of using full table names to qualify the columns like publishers.publisher_id = books.publisher_id, we use the table aliases:

SELECT 
    b.title, 
    b.isbn, 
    p.name
FROM 
    books b
    INNER JOIN publishers p 
        ON p.publisher_id = b.publisher_id
ORDER BY 
    title;
Code language: SQL (Structured Query Language) (sql)

In this example, we used b as the table alias for the books table, and p as the table alias for publishers table.

You can also use the table alias to explicitly specify the column from which table you want to select as shown in the following query:

SELECT 
    b.title, 
    b.isbn, 
    p.publisher_id, 
    p.name
FROM 
    books b
    INNER JOIN publishers p 
        ON p.publisher_id = b.publisher_id
ORDER BY 
    title;
Code language: SQL (Structured Query Language) (sql)

Note that if you don’t use p.publisher_id, Db2 will issue the error “a reference to the column is ambiguous”

In this tutorial, you have learned about the Db2 alias including table and column aliases.