Why can't I use a table's alias and its original name in the same query when two tables are joined?

Question

Assume there are two tables created by:

CREATE TABLE emp
(
EMPNO int,
EMPNAME varchar(255),
JOB varchar(255),
DEPTNO int
);

CREATE TABLE dept
(
LOC varchar(255),
DEPTNO int
);

I want to find out what dept has no employ. I use a left join like this:

select dept.* 
from dept  
left join emp 
 on (dept.deptno=emp.deptno) 
where emp.empno is null;

But if I use an alias for dept or emp, then I can only use alias and cannot use the original table name. For instance:

select dept.* 
from dept as d 
left join emp 
 on (dept.deptno=emp.deptno) 
where emp.empno is null;

I get an error "no such table: dept" from sqlite.

If I run operations on one table, I can use alias and the original table name in the same query.

Anybody know why?

Answer 1

Once you assign an alias to a table, that's its new name during the query - the original name is then not available.

The "why" is due to the SQL standard. If you're looking for an explanation of why would it have been specified to work that way, the main reason I can think of is if you join a table to itself, you need to alias at least one reference to distinguish them, but if you were allowed to also use the original name, it would be ambiguous as it could refer to either reference.

Also, if you want to use the original name, don't assign an alias