If there are common attributes in the table in the FROM clause of inner and outer query? Code Answer

Hello Developer, Hope you guys are doing great. Today at Tutorial Guruji Official website, we are sharing the answer of If there are common attributes in the table in the FROM clause of inner and outer query? without wasting too much if your time.

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?

Answer

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).

We are here to answer your question about If there are common attributes in the table in the FROM clause of inner and outer query? - If you find the proper solution, please don't forgot to share this with your team members.

Related Posts