This SQL tuning rule is more about SQL clarity, maintainability, and avoiding mistakes than about a major Explain Plan change.
Many Oracle interviewers ask:
"Why should we always use table aliases in joins?"
Because it avoids ambiguity, prevents errors, and makes Oracle parse the query more efficiently.
Sample Tables
EMP
| EMPNO | ENAME | DEPTNO |
|---|---|---|
| 7369 | SMITH | 20 |
| 7499 | ALLEN | 30 |
| 7566 | JONES | 20 |
| 7698 | BLAKE | 30 |
DEPT
| DEPTNO | DNAME |
|---|---|
| 10 | ACCOUNTING |
| 20 | RESEARCH |
| 30 | SALES |
Scenario 1: Bad Practice
Developer writes:
SELECT EMPNO,
ENAME,
DNAME
FROM EMP,
DEPT
WHERE DEPTNO = DEPTNO;
Oracle returns:
ORA-00918: column ambiguously defined
Why?
Because Oracle sees:
DEPTNO
in both tables.
EMP.DEPTNO
DEPT.DEPTNO
Oracle asks:
Which DEPTNO do you mean?
Oracle cannot guess.
Correct Query
SELECT e.EMPNO,
e.ENAME,
d.DNAME
FROM EMP e,
DEPT d
WHERE e.DEPTNO = d.DEPTNO;
Now Oracle clearly understands:
e.DEPTNO = EMP.DEPTNO
d.DEPTNO = DEPT.DEPTNO
No confusion.
Explain Plan
EXPLAIN PLAN FOR
SELECT e.EMPNO,
e.ENAME,
d.DNAME
FROM EMP e,
DEPT d
WHERE e.DEPTNO = d.DEPTNO;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
Plan:
------------------------------------------------
| Id | Operation | Name |
------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | HASH JOIN | |
| 2 | TABLE ACCESS FULL | EMP |
| 3 | TABLE ACCESS FULL | DEPT |
------------------------------------------------
Notice:
HASH JOIN
Oracle knows exactly which columns to join.
Scenario 2: Wrong Column Referenced
Suppose EMP has:
EMPNO
ENAME
DEPTNO
and DEPT has:
DEPTNO
DNAME
Developer writes:
SELECT *
FROM EMP e,
DEPT d
WHERE DEPTNO = 20;
Oracle sees:
DEPTNO = 20
but which DEPTNO?
EMP.DEPTNO ?
DEPT.DEPTNO ?
Error:
ORA-00918
Correct Version
Filter Employee Department
SELECT *
FROM EMP e,
DEPT d
WHERE e.DEPTNO = d.DEPTNO
AND e.DEPTNO = 20;
or
Filter Department Table
SELECT *
FROM EMP e,
DEPT d
WHERE e.DEPTNO = d.DEPTNO
AND d.DEPTNO = 20;
Much clearer.
Scenario 3: Self Join (Very Important)
Suppose EMP table contains managers.
| EMPNO | ENAME | MGR |
|---|---|---|
| 7369 | SMITH | 7902 |
| 7499 | ALLEN | 7698 |
| 7566 | JONES | 7839 |
| 7839 | KING | NULL |
Need Employee and Manager Names.
Wrong Query
SELECT ENAME,
ENAME
FROM EMP,
EMP
WHERE EMPNO = MGR;
Oracle immediately fails.
Because Oracle cannot identify:
Which EMP table?
Correct Query
SELECT e.ENAME Employee,
m.ENAME Manager
FROM EMP e,
EMP m
WHERE e.MGR = m.EMPNO;
Result:
| Employee | Manager |
|---|---|
| SMITH | FORD |
| ALLEN | BLAKE |
| JONES | KING |
Explain Plan
---------------------------------------------------
| Id | Operation | Name |
---------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | HASH JOIN | |
| 2 | TABLE ACCESS FULL | EMP |
| 3 | TABLE ACCESS FULL | EMP |
---------------------------------------------------
Oracle clearly knows:
EMP e
EMP m
are two different copies of the same table.
Real Production Example
Imagine a query joining:
CUSTOMERS
ORDERS
PAYMENTS
SHIPMENTS
PRODUCTS
Without aliases:
SELECT CUSTOMER_ID,
ORDER_ID,
PRODUCT_ID
...
After 6 months nobody knows:
CUSTOMER_ID from which table?
ORDER_ID from which table?
Debugging becomes difficult.
Best Practice
Always use aliases.
Bad:
SELECT EMP.EMPNO,
EMP.ENAME,
DEPT.DNAME
FROM EMP,
DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO;
Good:
SELECT e.EMPNO,
e.ENAME,
d.DNAME
FROM EMP e
JOIN DEPT d
ON e.DEPTNO = d.DEPTNO;
Shorter and easier to read.
Does Alias Improve Performance?
For small queries:
NO
Explain Plan remains the same.
Example:
Without Alias:
EMP.DEPTNO = DEPT.DEPTNO
With Alias:
e.DEPTNO = d.DEPTNO
Plan:
HASH JOIN
TABLE ACCESS FULL EMP
TABLE ACCESS FULL DEPT
Same plan.
The benefit is:
- Avoid ORA-00918 errors
- Prevent wrong-column mistakes
- Easier code review
- Easier query tuning
- Easier maintenance
- Essential for self-joins
Interview Answer
Why should columns be referenced using aliases when multiple tables are involved?
Answer:
Using aliases removes ambiguity, prevents ORA-00918 errors, makes joins easier to read, simplifies maintenance, and helps developers clearly identify which table each column belongs to. While aliases usually do not change the execution plan, they significantly reduce coding mistakes and improve SQL readability.
