What is the difference in select with alias, and without- oracle 11g
You have to because that's what the docs say ;)
2.1.8.1 When Table Aliases Are Required
Table aliases can be required to avoid problems resolving references.
Oracle Database requires you to use a table alias to qualify any dot-notational reference to subprograms or attributes of objects, to avoid inner capture and similar problems resolving references.
OK, so why does this rule exist?
Consider this scenario:
create type emp_obj as object (
employee_id integer,
employee_name varchar2(100),
department_id integer
);
/
create table departments (
department_id integer,
manager emp_obj
);
/
create table manager (
employee_id integer,
employee_name varchar2(100),
department_id integer
);
select * from departments d
where exists (
select * from manager
where manager.department_id = d.department_id --which manager.department_id?
);
In this example
- The
departments
table has amanager
object column with adepartment_id
attribute - The
manager
table has a columndepartment_id
So what does where manager.department_id
resolve to?!
In the absence of aliases, it could be either table. When you store objects it's possible to have:
<table_name>.<column_name>
identical to <object_column_name>.<attribute_name>
from another table in the same query!
When you start adding/removing columns from a table or attributes from a type this creates name resolution... surprises.
So to avoid this Oracle Database enforces that you must use aliases.
Just like how you need to use aliases when you use the same table twice in a query:
create table t (
c1 int, c2 int
);
select * from t, t
where c1 = 1;
ORA-00918: column ambiguously defined
select * from t t1, t t2
where t1.c1 = 1;
no rows selected
Note there is a refinement to the rule:
Use of a table alias is optional when referencing top-level attributes of an object table directly, without using the dot notation. For example, the following statements define two tables that contain the person_typ object type. person_obj_table is an object table for objects of type person_typ, and contacts is a relational table that contains a column of the object person_typ.
When you create an object table, the attributes are the columns. So the ambiguities above go away.