I'm using SQL*Plus: Release 8.1.5.0.0 with Oracle 8i.
I'm relatively to SQL and am failing to understand how the following could be possible:
When I run this query:
from s, p, j, spj
where s.sid = spj.sid and
p.pid = spj.pid and
j.jid = spj.jid
sql*plus reports:
*
ERROR at line 1:
ORA-00918: column ambiguously defined
I couldn't understand this, since the SELECT clause is evaluated last (according to the SQL spec). Therefore, when the SELECT clause is evaluated, it will be operating on a table returned by the remainder of the query which WILL have a column name PID.
This query shed some light:
from s, p, j, spj
where s.sid = spj.sid and
p.pid = spj.pid and
j.jid = spj.jid
So the join of the four table's does in fact return duplicate columns (there are two SID, PID and JID columns). It seems to defy the definition of a relational JOIN.
Now for my main question (sorry for all the rhetoric). I know that this query will return the result I want (A JOIN over the 4 tables):
from s, p, j, spj
where s.sid = spj.sid and
p.pid = spj.pid and
j.jid = spj.jid
Why do I have to sepecify SELECT <table>.<column name>, ...??? Since as I mentioned earlier, the select stament is evaluated last and (to my mind) should not have (or need) knowledge of the original tables that contributed to the result it is operating on?
I'm relatively to SQL and am failing to understand how the following could be possible:
When I run this query:
Code:
select jid, jname, pid, pname, qty from s, p, j, spj
where s.sid = spj.sid and
p.pid = spj.pid and
j.jid = spj.jid
sql*plus reports:
Code:
select jid, jname, pid, pname, qty *
ERROR at line 1:
ORA-00918: column ambiguously defined
I couldn't understand this, since the SELECT clause is evaluated last (according to the SQL spec). Therefore, when the SELECT clause is evaluated, it will be operating on a table returned by the remainder of the query which WILL have a column name PID.
This query shed some light:
Code:
select * from s, p, j, spj
where s.sid = spj.sid and
p.pid = spj.pid and
j.jid = spj.jid
So the join of the four table's does in fact return duplicate columns (there are two SID, PID and JID columns). It seems to defy the definition of a relational JOIN.
Now for my main question (sorry for all the rhetoric). I know that this query will return the result I want (A JOIN over the 4 tables):
Code:
select j.jid, j.jname, p.pid, p.pname, spj.qty from s, p, j, spj
where s.sid = spj.sid and
p.pid = spj.pid and
j.jid = spj.jid
Why do I have to sepecify SELECT <table>.<column name>, ...??? Since as I mentioned earlier, the select stament is evaluated last and (to my mind) should not have (or need) knowledge of the original tables that contributed to the result it is operating on?
