Issue with Parent-Child Relationship in Sql-Server

Question

Suppose I have two tables with parent-child relationship in sql server as below,

parent table:

Parentid  value 

1         demo
2         demo2

child table:

childid     parchildid   subvalue

1           1            demo1
2           1            demo2

here parchildid from child table is a foreign key referring parentid of the parent table.

I needed to retrieve child table data for a particular parentid. So, I used below query

 select *from child
 where parchildid in (select parchildid from parent)

It gave the below output. ( all the rows for child table)

 childid    parchildid   subvalue

  1         1            demo1
  2         1            demo2

But as you see, I have given a invalid column (parchildid) in the sub-query ( parchildid belongs to child table not the parent table ).

I wonder why sql server didn't throw any error.

running select parchildid from parent query alone thows invalid column error.

could anyone explains why there is no error thrown in the sub-query? hows the logic works there?

Thanks

Answer 1

From MSDN:

If a column does not exist in the table referenced in the FROM clause of a subquery, it is implicitly qualified by the table referenced in the FROM clause of the outer query.

In your case, since parchildid is a column from the table in the outer query, there is no error. On it's own however, the query cannot find such a column, and so it fails.

Answer 2

It is equivalent to writing:

select *
  from child c
 where c.parchildid in 
     (
        select c.parchildid
          from parent p
     )

If you notice, child has an alias of c which is accessible inside the subquery.

It is also like writing:

select *
  from child c
 where Exists 
     (
        select *
          from parent p
         where c.parchildid = c.parchildid
     )