Hi Friends
Can we rewrite sub-queries into simple select statements or with joins ?
Loading
Know the answer? Post it — somebody with the same question will find it here.
Sign in to answer this question
It is the same account you read, post and publish with — and you will come straight back to this page.
Nitesh KejriwalPosted Oct 15, 2012, 7:10 AM
SubQuery
========
SELECT a.* FROM tb1 a WHERE a.col1 IN (SELECT b.col1 FROM tbl2 b)
Joins
====
SELECT a.*,b.* FROM
tbl1 a JOIN tbl2 b
ON a.col1=b.col1
With Joins, you can retrieve any columns of the joining tables, while with sub queries, this is not possible.
Santhosh Kumar JayaramanPosted Oct 15, 2012, 7:02 AM
Select * from tbl1 where id in (select id from tbl2 where status='AC')
You can rewrite it as
select tbl1.* from tbl1
inner join tbl2 on tbl2.Id=tbl1.id
where tbl2.status='AC'