Aliases

The Kognitio SQL implementation allows the use of aliases for tables and column names. Both can take AS before the alias name.

Table Aliases

Table aliases are used in queries as SQL identifiers in order to ease typing, especially when accessing more than one table:

SELECT A.col1,
       A.col2,
       B.col1
FROM table_A A,
     table_B B
WHERE A.col1 = B.col1

Here, table_A is referred to just by the alias A, and table_B by the alias B. Aliases follow the same naming conventions as columns, so they must start with a letter, but can contain digits and the underscore character. Reserved words are not permitted for alias names.

The Kognitio training data is used in the examples below.

Example 1: Table Name Aliases

Consider the following query:

SELECT p.p_partkey,
       s.s_name,
       p.p_name,
FROM part p,
     supplier s,
     partsupp ps
WHERE p.p_partkey = ps.ps_partkey
AND s.s_suppkey = ps.ps_suppkey
ORDER BY 1, 2

Here, including the alias in the select_list is optional, as the columns have distinct names. However, if the tables were defined so that the supplier and part tables each had a column called “name” (without the s and p to distinguish them), it would be essential to include the table name as an identifier to avoid ambiguity.

Example 2: Using Aliases in a Self-Join

This example finds the cheapest part from each supplier in the partsupp table using a self-join. In this case table aliases are required to allow us to individually identify the two copies of the table:

SELECT a.ps_partkey AS part,
       a.ps_suppkey AS supplier,
       a.ps_supplycost AS cost
FROM partsupp AS a, partsupp AS b
WHERE a.ps_suppkey = b.ps_suppkey
AND a.ps_supplycost >= b.ps_supplycost
GROUP BY 1, 2, 3
HAVING COUNT(*) = 1
ORDER BY 1, 2, 3

Column Aliases

In a SELECT statement, you either select column references or expressions. Columns have names but expressions don’t, so you can use an AS clause to

  • Label an expression

  • Re-label a column.

A few important things to remember:

  • The name given in the AS clause overrides any name the item may have had previously

  • In an AS clause, you cannot use a name that has already been used in a SELECT

  • If you use a name that is identical to a column name as an AS variable and then reference it later in the query, it is resolved in favor of the column name. This can cause confusion and should be avoided. See example below

  • An alias can be used in an ORDER BY clause

  • An alias CANNOT be used in a WHERE, GROUP BY or HAVING clause

  • You can actually omit the word AS in an AS clause.

Example 1: Labelling Simple Expressions

You can use a simple expression to calculate a discounted price, based on the retail price for all items in the part table, then use an AS clause to label the new column:

SELECT p_partkey,
       p_name,
       p_retailprice,
       (p_retailprice * 0.95) AS discount_price
FROM part
ORDER BY 1

Example 2: Labeling the Result Column for CASE

This example displays the order numbers, date of order, order priority, and a column labeled ShipBy, which determines when the order should be delivered based on the order priority:

SELECT o_orderkey,
       o_orderdate,
       o_orderpriority,
       CASE LOWER(o_orderpriority)
           WHEN 'high' THEN o_orderdate + INTERVAL '1' DAY
           WHEN 'med' THEN o_orderdate + INTERVAL '7' DAY
           WHEN 'low' THEN o_orderdate + INTERVAL '1' MONTH
       END AS ShipBy
FROM ordertab
ORDER BY o_orderkey

Example 3: Confusion when Alias Names Duplicate Column Names

Neither ANSI ‘89 nor SQL92 have firm rules about naming columns in the AS clauses. So, the following is a confusing, but legal statement, even though it uses ps_partkey and ps_suppkey as aliases when both are also column names:

SELECT a.ps_partkey AS ps_suppkey,
       a.ps_suppkey AS ps_partkey,
FROM partsupp AS a

Needless to say this sort of aliasing leads to confusion and should be avoided at all costs.