By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,377 Members | 5,322 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,377 IT Pros & Developers. It's quick & easy.

Sub Query and join Difference

100+
P: 111
hi,

In these two Query, for join we have to use DISTINCT but for SubQuery no need to use distinct.



SELECT DISTINCT PM.PropertyID, PM.PropertyName FROM PropertyMaster PM
JOIN PropertyManager PMgr ON PM.PropertyID = PMgr.PropertyID
JOIN CompanyRegistrationMaster CRM ON PMgr.CompanyID = CRM.CompanyID
WHERE Master = @CompanyID

SELECT PropertyName, PropertyID FROM PropertyMaster WHERE PropertyID IN
(SELECT PropertyID FROM PropertyManager WHERE CompanyID IN
(SELECT companyid FROM CompanyRegistrationMaster
WHERE master = 47))

Anybody explain me what is the difference

Thanks
Apr 18 '08 #1
Share this Question
Share on Google+
2 Replies


deepuv04
Expert 100+
P: 227
hi,

In these two Query, for join we have to use DISTINCT but for SubQuery no need to use distinct.



SELECT DISTINCT PM.PropertyID, PM.PropertyName FROM PropertyMaster PM
JOIN PropertyManager PMgr ON PM.PropertyID = PMgr.PropertyID
JOIN CompanyRegistrationMaster CRM ON PMgr.CompanyID = CRM.CompanyID
WHERE Master = @CompanyID

SELECT PropertyName, PropertyID FROM PropertyMaster WHERE PropertyID IN
(SELECT PropertyID FROM PropertyManager WHERE CompanyID IN
(SELECT companyid FROM CompanyRegistrationMaster
WHERE master = 47))

Anybody explain me what is the difference

Thanks
Hi,
I think in the join we are maping a relation between two tables and retriving the data based on that relations. here the relation is one to many, and in join you get a record as many times it appears in the chaild table.
In subquery we are retriving the data from a single table where the given condition is satisfied. in this case the record exists in the table only once.

this what i am thinking. any experts comments please....

thanks
Apr 18 '08 #2

ck9663
Expert 2.5K+
P: 2,878
hi,

In these two Query, for join we have to use DISTINCT but for SubQuery no need to use distinct.



SELECT DISTINCT PM.PropertyID, PM.PropertyName FROM PropertyMaster PM
JOIN PropertyManager PMgr ON PM.PropertyID = PMgr.PropertyID
JOIN CompanyRegistrationMaster CRM ON PMgr.CompanyID = CRM.CompanyID
WHERE Master = @CompanyID

SELECT PropertyName, PropertyID FROM PropertyMaster WHERE PropertyID IN
(SELECT PropertyID FROM PropertyManager WHERE CompanyID IN
(SELECT companyid FROM CompanyRegistrationMaster
WHERE master = 47))

Anybody explain me what is the difference

Thanks

Join pairs every row on the left to the record on the right. The "pairing" would depend if you're trying to get all rows that have pairs or all the ones on the left and the "paired" ones or all the ones on the right and the "paired" ones or everything.

Your subquery checks for existence. Although a WHERE can be used to join multiple tables, your query just check if an argument exists on the other tables.

-- CK
Apr 18 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.