Why should we always use table aliases in joins?

 

Wherever multiple tables are used, always refer to a column by either using an alias or using the fully qualified name. Do not leave the guess work for Oracle.

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.