{"id":260,"date":"2019-03-31T02:05:14","date_gmt":"2019-03-31T10:05:14","guid":{"rendered":"https:\/\/db2tutorial.com\/?page_id=260"},"modified":"2020-04-11T06:59:51","modified_gmt":"2020-04-11T14:59:51","slug":"db2-alias","status":"publish","type":"page","link":"https:\/\/www.db2tutorial.com\/db2-basics\/db2-alias\/","title":{"rendered":"Db2 Alias"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the Db2 alias including column alias and table alias in the query.<\/p>\n\n\n\n

Db2 column alias<\/h2>\n\n\n\n

When you use the SELECT<\/a><\/code> statement to query data from a table, Db2 use the column names as the headings for the output:<\/p>\n\n\n

SELECT<\/span> \n    first_name, \n    last_name\nFROM<\/span> \n    authors<\/span>\nORDER<\/span> BY<\/span> \n    first_name, \n    last_name;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the output:<\/p>\n\n\n\n
\"Db2<\/figure>\n\n\n\n
However, you can change the column headings by giving them aliases using the following syntax:<\/p>\n\n\n
{column | expression} AS column_alias\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The AS<\/code> keyword is optional.<\/p>\n\n\n\n
For example, the following statement uses column aliases to change column headings from first_name<\/code> to \"Author First Name\"<\/code> and last_name<\/code> to \"Author Last Name\"<\/code>. Because the column aliases contain spaces, we have to surround it with double quotes (“):<\/p>\n\n\n
SELECT<\/span> \n    first_name AS<\/span> \"Author First Name\"<\/span>, \n    last_name AS<\/span> \"Author Last Name\"<\/span>\nFROM<\/span> \n    authors<\/span>\nORDER<\/span> BY<\/span> \n    first_name, \n    last_name;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"Db2<\/figure>\n\n\n\n
Once column aliases are assigned, they can be referenced in the ORDER BY<\/a><\/code> clause.<\/p>\n\n\n\n
This example uses a column alias (full_name<\/code>) to an expression that concatenates the first name, the middle name, and the last name separated by a space:<\/p>\n\n\n
SELECT<\/span> \n    first_name || ' '<\/span> || middle_name || ' '<\/span>  || last_name full_name \nFROM<\/span>\n    authors<\/span>\nORDER<\/span> BY<\/span> \n    full_name;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the output:<\/p>\n\n\n\n
\"Db2<\/figure>\n\n\n\n
Db2 table alias<\/h2>\n\n\n\n
Similar to a column, you can assign a table an alias using the following syntax:<\/p>\n\n\n
table_name AS table_alias\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The AS<\/code> keyword in this syntax is also optional.<\/p>\n\n\n\n
Once the table is assigned an alias, you can refer its columns using the following syntax:<\/p>\n\n\n
table_alias.column_name\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
This query joins two tables: books<\/code> and authors<\/code>. Instead of using full table names to qualify the columns like publishers.publisher_id = books.publisher_id<\/code>, we use the table aliases:<\/p>\n\n\n
SELECT<\/span> \n    b.title, \n    b.isbn, \n    p.name\nFROM<\/span> \n    books b\n    INNER<\/span> JOIN<\/span> publishers p \n        ON<\/span> p.publisher_id = b.publisher_id\nORDER<\/span> BY<\/span> \n    title;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this example, we used b<\/code> as the table alias for the books<\/code> table, and p<\/code> as the table alias for publishers<\/code> table.<\/p>\n\n\n\n
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:<\/p>\n\n\n
SELECT<\/span> \n    b.title, \n    b.isbn, \n    p.publisher_id, \n    p.name\nFROM<\/span> \n    books b\n    INNER<\/span> JOIN<\/span> publishers p \n        ON<\/span> p.publisher_id = b.publisher_id\nORDER<\/span> BY<\/span> \n    title;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Note that if you don’t use p.publisher_id<\/code>, Db2 will issue the error “a reference to the column is ambiguous”<\/p>\n\n\n\n
In this tutorial, you have learned about the Db2 alias including table and column aliases.<\/p>\n
\n\t
\n\t\t
Was this tutorial helpful ?<\/div>\n\t\t
\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
\n\t\t
\n\t\t\t
<\/div>\n\t\t\t\n\t\t\t
We often use the table aliases in the queries that involve JOIN<\/a><\/code> clause to make the query shorter.<\/p>\n\n\n\n