Tables and Column Aliases

Oracle Community

Tables and Column Aliases

Table Aliases

Table aliases give you the ability to clarify which table you are referring to in a query, and to do so with an abbreviated name or code. Use table aliases in all but trivial, single-table queries. Ensure that your table aliases are short, but intuitive. Use them consistently. Do not use them for the one or two ambiguous columns in your query which the compiler required you to clarify.

In the following examples, we've lengthened the table names in the SCOTT schema to illustrate the use of short aliases that don't duplicate the table name. Assume the company name is FYZXX, Inc.

SELECT ename  emp_name,
       job    job_name,
       dname dept_name,
       loc   location
  FROM fyzxx_employee   a,
       fyzxx_department b
 WHERE a.deptno = b.deptno
 ORDER BY b.deptno,
          a.sal DESC;

In this example, the aliases chosen are too short, not intuitive, and aren't used consistently. If they are going to be this short, at least use a letter that can be intuitively mapped to the table it represents, like "e" for the employee table, and "d" for the department table. However, queries are rarely this simple; you may be joining more than one table that could be interpreted as the "e" table. So you should use 2 to 5 characters for your table aliases, to ensure clarity, readability and maintainability, e.g.

SELECT emp.ename  emp_name,
       emp.job    job_name,
       dept.dname dept_name,
       dept.loc   location
  FROM fyzxx_employee   emp,
       fyzxx_department dept
 WHERE emp.deptno = dept.deptno
 ORDER BY dept.deptno,
          emp.sal DESC;

Some tools will automatically prepend every column name with the full name of the table as the table "alias." SQL like the following often shows up in tools that cater to Access and SQL-Server:

SELECT "FYZXX_EMPLOYEE"."ENAME" "EMP_NAME",
       "FYZXX_EMPLOYEE"."JOB" "JOB_NAME",
       "FYZXX_DEPARTMENT"."DNAME" "DEPT_NAME",
       "FYZXX_DEPARTMENT"."LOC"   "LOCATION"
  FROM "FYZXX_EMPLOYEE",
       "FYZXX_DEPARTMENT"
 WHERE "FYZXX_EMPLOYEE"."DEPTNO" = "FYZXX_DEPARTMENT"."DEPTNO"
 ORDER BY "FYZXX_DEPARTMENT"."DEPTNO",
          "FYZXX_EMPLOYEE"."SAL" DESC;

Do not allow this style in production code, as it is nearly unreadable, much longer than it needs to be, and more difficult to maintain.

Finally, when unnesting PL/SQL collections or nested table columns, aliases are very important. First, scalar collections have no column name. The single, implied field in a scalar collection can only be referred to in SQL using the pseudo-column COLUMN_VALUE. It is a best practice to give the pseudo-column a column alias. Second, in earlier versions of 9i, unless you used a table alias for the unnested collection, weird and seemingly random errors would pop up. This seems to be fixed in 10g, but it is still a good idea to alias whatever gets wrapped with the TABLE() operator. Study the following example:

CREATE OR REPLACE TYPE type_currency_tab AS TABLE OF NUMBER(12,4)
/
SET SERVEROUTPUT ON
DECLARE
   l_prices type_currency_tab := type_currency_tab(5.50, 10.95, 995.99);
BEGIN
   FOR lrec IN (SELECT curr.COLUMN_VALUE AS price
                FROM TABLE(l_prices) curr
               WHERE curr.COLUMN_VALUE > 100) LOOP
      dbms_output.put_line('Large Price: ' || lrec.price);
   END LOOP;
END;
/

The Asterisk

The dynamic SQL column reference “*” gives you a way to refer to all of the columns of a table, view or materialized view. Oracle simply refers to this as the asterisk operator. For example, if you specify:

SELECT * FROM emp;

Oracle retrieves every column in the EMP table. When fetching the results of the query, Tthe SQL parser transparently obtains all the column names from the data dictionary. As the number and order of the columns change, the SELECT list can be left as-is; it dynamically adjusts. This can be both useful and error prone.

Useful in that certain application functionality is easier to maintain. For instance, if auditing data changes using triggers and a mirrored audit table, instead of referring to every column in the INSERT list, you can insert the audit metadata with a few built-in functions, and then the asterisk for everything else:

INSERT INTO emp_audit
SELECT USER, SYSDATE, e.*
FROM   emp e
WHERE  emp_no = i_emp_no;

If you keep the audit table up to date (i.e., identical in column number, type, and sequence to the source table), you will never need to maintain the audit routine(s).

Use of the asterisk is error prone in that unless the consumer of the dynamically adjusting list is also altered, things will break. If the front-end grid, screen or report isn't altered to accommodate the change, it will cease working. If the backend audit table used to track column changes isn't altered, the INSERT in the audit trigger will throw an ORA-00913 (too many values), 00904 (invalid identifier) or 00947 (not enough values). Since code that relies on the asterisk still compiles after a table change, these errors are typically not found until runtime, sometimes not until the product is already in production.

The asterisk is also inefficient, since it fetches all the bytes for every column, instead of just the columns that are actually needed.

So unless your application requires a loosely-typed cursor that dynamically adjusts to a changing table, you should never use the asterisk to refer to all the columns in a SELECT or INSERT as it results in fragile code, code on which it is impossible to do column-change impact analysis. Many Oracle shops, in their programming standard, ban the use of the asterisk altogether.

Column Aliases

You can alter the name of a column returned in a result set by using a column alias. This is useful for a variety of reasons.

Perhaps you are changing the names of some columns in order to comply with a new standard, or to render them more consistent, but you don't have the resources or permission to change all the references to that column in systems that depend on that table. In this case you can simply give the new column name an alias with the old name to prevent the application from breaking. A few examples of this are given below:

SELECT empno,
       socsecno ssn
  FROM emp;

SELECT empno,
       socsecno AS ssn
  FROM emp;

SELECT empno,
       socsecno AS "SSN"
  FROM emp;

The quoted version of an alias, as seen in the last example above, is more useful in reports where a stylized, capitalized or otherwise custom column header is required.

Perhaps you have a complex query that is joining multiple tables, several of which have equivalent column names in them. This is particularly problematic in shops that name all their surrogate, sequence-filled primary keys with the same name, like "ID". Oracle gets confused and complains of ambiguous column names. For data models like this, use table and column aliases abundantly to clarify the source, as seen in the following example:

SQL> CREATE TABLE tab1 (ID NUMBER NOT NULL PRIMARY KEY, val1 VARCHAR2(10));
 
Table created
 
SQL> CREATE TABLE tab2 (ID NUMBER NOT NULL PRIMARY KEY, val2 VARCHAR2(10));
 
Table created
 
SQL> INSERT INTO tab1 VALUES(1,'Hello');
 
1 row inserted
 
SQL> INSERT INTO tab2 VALUES(1,'World');
 
1 row inserted
 
SQL> commit;
 
Commit complete
 
SQL>
SELECT id, val1, id, val2
  FROM tab1, tab2
 WHERE id = id;

ORA-00918: column ambiguously defined
 
SQL>
SELECT id, val1, id, val2
  FROM tab1 t1, tab2 t2
 WHERE t1.id = t2.id;

ORA-00918: column ambiguously defined
 
SQL>
SELECT t1.id, val1, t2.id, val2
  FROM tab1 t1, tab2 t2
 WHERE t1.id = t2.id;
 
        ID VAL1               ID VAL2
---------- ---------- ---------- ----------
         1 Hello               1 World

Oracle finally knows which ID columns are being referred to, but the result set has two ID column names, which will probably confuse or break the consuming application. Use column aliases to clarify which ID column is being referred to:

SQL>
SELECT t1.id tab1_id, t1.val1, t2.id tab2_id, t2.val2
  FROM tab1 t1, tab2 t2
 WHERE t1.id = t2.id;
 
   TAB1_ID VAL1          TAB2_ID VAL2
---------- ---------- ---------- ----------
         1 Hello               1 World

15807 1 /
Follow / 2.11.2014 at 6:04pm

great!

what kind of tools (shortcuts) are ther in TOAD to create, rename or remove table or column aliases? i.e. I forgot to use an alias, I'd like to create an alias for the table and TOAD to rewrite every column to include the alias :)