CODEWITHSUNDEEP
sqloracle-database
Jörg
Jörg

Reputation: 71

SQL with ambiguous column

Check the following example (Oracle syntax):

create table t1 (id number, a varchar2(255));
create table t2 (id number, a varchar2(255));
create table t3 (id number);

select * from t1 where id in (select id from t2 where a = 'aa');
select * from t1 where id in (select id from t3 where a = 'aa');

Both select work fine but they use different attributes for filtering. In my opinion first SQL should give an error because column a is ambiguously defined. Is this not defined in the official SQL standard?

Upvotes: 1

Views: 2595

Answers (3)

user5683823
user5683823

Reputation:

The behavior is exactly what it should be, based on long-standing principles of programming. I haven't seen the SQL standard (apparently it can't be inspected for free), but in programming in general the "collision" between names is one of the most common issues, and at least for some situations there are very simple rules. This is one of them: a local name will always mask the same name that may exist in the calling environment.

The sad thing is, Oracle itself doesn't always follow this simple rule. There are some bugs related to this in factored subqueries (there has been some discussion of this on OTN, resulting in filing bug reports with Oracle).

Upvotes: 1

Alex Poole
Alex Poole

Reputation: 191265

It isn't the clearest statement, but the documentation does refer to this behaviour:

If columns in a subquery have the same name as columns in the containing statement, then you must prefix any reference to the column of the table from the containing statement with the table name or alias. To make your statements easier to read, always qualify the columns in a subquery with the name or alias of the table, view, or materialized view.

So essentially that's saying that for your first statement to reference t1.a you would have to explicitly use it's name (or alias):

select * from t1 where id in (select id from t2 where t1.a = 'aa');

If you don't then it will use the subquery's table by default. It isn't ambiguous to the parser because it will look at the tables in the same level of (sub)query first, and only look at the outer level if it can't find that column in the current level. That might not be what you're expecting, and I agree complaining that it is ambiguous would be better to avoid subtle mistakes that give wrong results; but it is how it works.

But as it also says, it's safer to always be explicit anyway:

select * from t1 where t1.id in (select t2.id from t2 where t2.a = 'aa');

or

select * from t1 where t1.id in (select t2.id from t2 where t1.a = 'aa');

and for your second statement:

select * from t1 where t1.id in (select t3.id from t3 where t1.a = 'aa');

Upvotes: 5

Suraj
Suraj

Reputation: 3127

The first SQL.

select * from t1 where id in (select id from t2 where a = 'aa');

The first query will take t2 table's "a" column while executing the query. As we know that both the tables t1 and t2 have same column name but the preferences given to local column.

Second SQL.

select * from t1 where id in (select id from t3 where a = 'aa');

While executing the second query it will select column "a" from table t1.

Because there is not any column named "a" and hence the SQL will take different attribute to execute the query.

Upvotes: -1

Related Questions