Are JOINs and WHEREs semantically equivalent?

Hi,
I have a query that uses a series of JOINs to produce a result set.
However, it needs to be generated by a custom SQL Builder, and it would be
easier to auto-generate if the JOINs were WHERE clauses instead.
Is this possible - i.e. can I do *everything* that a JOIN does in a WHERE,
and still get *exactly* the same result?
The query is below, if it helps to shed some light on the above question:
SELECT COUNT(DISTINCT(I1.ID)) AS CountOfIncidents,
OuterDepartment.CalculatedPath as Department
,OuterSource.CalculatedPath as OuterSource
FROM Incident I1
LEFT JOIN
yDepartment OuterDepartment ON I1.RaisedAgainstDepartmentID IN
(SELECT yDepartment.ID
FROM yDepartment
WHERE (yDepartment.CalculatedPath =
OuterDepartment.CalculatedPath) OR
(yDepartment.CalculatedPath
LIKE OuterDepartment.CalculatedPath + '\%'))
LEFT JOIN
yIncidentSourceType OuterSource ON I1.IncidentSourceTypeID IN
(SELECT yIncidentSourceType.ID
FROM yIncidentSourceType
WHERE (yIncidentSourceType.CalculatedPath =
OuterSource.CalculatedPath) OR
(yIncidentSourceType.CalculatedPath
LIKE OuterSource.CalculatedPath + '\%'))
WHERE (OuterDepartment.ParentID = 12) AND (OuterSource.ParentID is null)
AND
I1.[ID] IN (SELECT DISTINCT I1.[ID] FROM [Incident]I1)
GROUP BY OuterDepartment.CalculatedPath, OuterSource.CalculatedPath
[The query sums totals of all children of specified parent records,
presenting them in a Matrix format]
Thanks,
Duncanhttp://www.tek-tips.com/faqs.cfm?fid=5168
"Duncan M Gunn" <gunnd@.gaelqualityNOSPAM.co.uk> wrote in message
news:e0wBGgcQFHA.3816@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a query that uses a series of JOINs to produce a result set.
> However, it needs to be generated by a custom SQL Builder, and it would be
> easier to auto-generate if the JOINs were WHERE clauses instead.
> Is this possible - i.e. can I do *everything* that a JOIN does in a WHERE,
> and still get *exactly* the same result?
>
> The query is below, if it helps to shed some light on the above question:
>
> SELECT COUNT(DISTINCT(I1.ID)) AS CountOfIncidents,
> OuterDepartment.CalculatedPath as Department
> ,OuterSource.CalculatedPath as OuterSource
> FROM Incident I1
> LEFT JOIN
> yDepartment OuterDepartment ON I1.RaisedAgainstDepartmentID IN
> (SELECT yDepartment.ID
> FROM yDepartment
> WHERE (yDepartment.CalculatedPath =
> OuterDepartment.CalculatedPath) OR
> (yDepartment.CalculatedPath
> LIKE OuterDepartment.CalculatedPath + '\%'))
> LEFT JOIN
> yIncidentSourceType OuterSource ON I1.IncidentSourceTypeID IN
> (SELECT yIncidentSourceType.ID
> FROM yIncidentSourceType
> WHERE (yIncidentSourceType.CalculatedPath
> = OuterSource.CalculatedPath) OR
> (yIncidentSourceType.CalculatedPath LIKE OuterSource.CalculatedPath +
> '\%'))
> WHERE (OuterDepartment.ParentID = 12) AND (OuterSource.ParentID is
> null)
> AND
> I1.[ID] IN (SELECT DISTINCT I1.[ID] FROM [Incident]I1)
> GROUP BY OuterDepartment.CalculatedPath, OuterSource.CalculatedPath
>
> [The query sums totals of all children of specified parent records,
> presenting them in a Matrix format]
>
> Thanks,
> Duncan
>|||Duncan,
To put the join expression in the WHERE clause you have to use the old style
join and it is not compatible for outer joins. For example, when you want to
select all rows from t1 with no entries in t2 using a join condition.
select a.*
from t1 as a left join t2 as b on a.pk_col = b.pk_col
where b.pk_col is null
-- this statement will not yield same result as previous
select a.*
from t1 as a, t2 as b
where a.pk_col *= b.pk_col and b.pk_isnull
go
ANSI JOIN vs. OUTER JOIN
http://www.microsoft.com/sql/techin...ment/July23.asp
AMB
"Duncan M Gunn" wrote:

> Hi,
> I have a query that uses a series of JOINs to produce a result set.
> However, it needs to be generated by a custom SQL Builder, and it would be
> easier to auto-generate if the JOINs were WHERE clauses instead.
> Is this possible - i.e. can I do *everything* that a JOIN does in a WHERE,
> and still get *exactly* the same result?
>
> The query is below, if it helps to shed some light on the above question:
>
> SELECT COUNT(DISTINCT(I1.ID)) AS CountOfIncidents,
> OuterDepartment.CalculatedPath as Department
> ,OuterSource.CalculatedPath as OuterSource
> FROM Incident I1
> LEFT JOIN
> yDepartment OuterDepartment ON I1.RaisedAgainstDepartmentID IN
> (SELECT yDepartment.ID
> FROM yDepartment
> WHERE (yDepartment.CalculatedPath =
> OuterDepartment.CalculatedPath) OR
> (yDepartment.CalculatedPat
h
> LIKE OuterDepartment.CalculatedPath + '\%'))
> LEFT JOIN
> yIncidentSourceType OuterSource ON I1.IncidentSourceTypeID IN
> (SELECT yIncidentSourceType.ID
> FROM yIncidentSourceType
> WHERE (yIncidentSourceType.CalculatedPath
=
> OuterSource.CalculatedPath) OR
> (yIncidentSourceType.Calcu
latedPath
> LIKE OuterSource.CalculatedPath + '\%'))
> WHERE (OuterDepartment.ParentID = 12) AND (OuterSource.ParentID is nul
l)
> AND
> I1.[ID] IN (SELECT DISTINCT I1.[ID] FROM [Incident]I1)
> GROUP BY OuterDepartment.CalculatedPath, OuterSource.CalculatedPath
>
> [The query sums totals of all children of specified parent records,
> presenting them in a Matrix format]
>
> Thanks,
> Duncan
>
>|||>> However, it needs to be generated by a custom SQL Builder, and it
would be
easier to auto-generate if the JOINs were WHERE clauses instead. Is
this possible <<
No. The OUTER JOINs have to be done in a sequence and cannot be moved
to the WHERE clause. The old *= syntax is deprecated and will not be
supported in the future.
[sic], presenting them in a Matrix format <<
The path enumeration model for a hierarchy is good for searching, but
terrible for aggregation. Consider changing to a nested sets model and
you can do this in one simple query.