CODEWITHSUNDEEP
sqloracle11g
mkbsc
mkbsc

Reputation: 107

Query explanation

I have a query similar to the following,

select * from T1 where abc in (select distinct abc from T2);

the scenario is that there is NO field named abc in table T2, so the inner query goes invalid, but still the whole query returns the output.

What is the reason for this behaviour?

Upvotes: 0

Views: 68

Answers (2)

Justin Cave
Justin Cave

Reputation: 231661

If there is no column abc in t2, the reference to abc in the subquery will be resolved by looking for a column abc in t1. So the query ends up being

select * 
  from T1 
 where t1.abc in (select distinct t1.abc 
                    from T2);

which is always true (assuming that T2 has at least 1 row and t1.abc is not NULL.

This is one of the many reasons that you should always qualify your column references. That way, you'll get a syntax error rather than getting an unexpected reference to a different table.

Upvotes: 2

Colin 't Hart
Colin 't Hart

Reputation: 7729

abc from T1 is visible to the subquery and can be listed in any expression on the table T2.

So for each row in T2, the value of abc for the current row in T1 will be returned.

Because you have distinct, the subselect will return just one row.

What is in effect 'abc in (abc)' will therefore always be true and hence you've effectively written

select * from T1;

Upvotes: 0

Related Questions