bmsqldev bmsqldev - 6 months ago 22
SQL Question

Issue with Parent-Child Relationship in Sql-Server

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

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