Sub Query and join Difference

[baburk]

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 CompanyRegistra tionMaster 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 CompanyRegistra tionMaster
WHERE master = 47))

Anybody explain me what is the difference

Thanks

[deepuv04]

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 CompanyRegistra tionMaster 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 CompanyRegistra tionMaster
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

[ck9663]

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 CompanyRegistra tionMaster 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 CompanyRegistra tionMaster
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