The question is published on by Tutorial Guruji team.
Schema for table A: A(x,y,z) Schema for table B: B(u,x,v)
[Primary keys mentioned in bold]
For the SQL query as mentioned:-
SELECT x FROM A WHERE x in ( SELECT x FROM B WHERE x<10)
How does the inner query resolve that this x mentioned is from the table B and not the table A?
x is resolved from the innermost query out. It is always better to qualify column names, so write this query as:
SELECT A.x FROM A WHERE A.x IN (SELECT B.x FROM B WHERE B.x < 10 );
This has the advantage that if
B.x does not exist, you will get an error. Otherwise, the
IN (SELECT x . . . will refer to
A.x (but only when
B.x does not exist).