Are these two statements equivalent?

Both return the same records in the same order, with the same values, and I
think I'm right, but I just wanted to get some other eyeballs on this so I
can win the argument we're having here, because mine (the second one) is
more than twice as fast.
-- Uses a WHERE...IN(...) and subquery on one of the joined tables
SELECT DISTINCT ss.SampleSourceKey, dbo.ParseFilename(ss.Filename) AS
Filename
FROM SampleSource ss WITH (NOLOCK)
INNER JOIN CLMR WITH (NOLOCK)
ON clmr.SampleSourceKey = ss.SampleSourceKey
WHERE ss.SampleSourceKey IN (
SELECT clmr.SampleSourceKey
FROM CLMR WITH (NOLOCK)
WHERE CountryKey IS NULL
)
ORDER BY ss.SampleSourceKey
-- Uses a column from one of the joined tables.
SELECT DISTINCT ss.SampleSourceKey, dbo.ParseFilename(ss.Filename) AS
Filename
FROM SampleSource ss WITH (NOLOCK)
INNER JOIN CLMR WITH (NOLOCK)
ON clmr.SampleSourceKey = ss.SampleSourceKey
WHERE clmr.CountryKey IS NULL
ORDER BY ss.SampleSourceKey
Peace & happy computing,
Mike Labosh, MCSD
"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave Mustanethe usual: if this query
SELECT clmr.SampleSourceKey
FROM CLMR WITH (NOLOCK)
WHERE CountryKey IS NULL
can ever return a null, then the first query will return nothing...|||> the usual: if this query
> SELECT clmr.SampleSourceKey
> FROM CLMR WITH (NOLOCK)
> WHERE CountryKey IS NULL
> can ever return a null, then the first query will return nothing...
Ooo I had forgotten that, but we're safe here, because clmr.SampleSourceKey
is required.
Thanks for the brainpick!
--
Peace & happy computing,
Mike Labosh, MCSD
"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave Mustane

Are these equivalent?

Are the 2 conditions below equivalent? Thanks
dbo.tblBillingDetail.AuthNo IS NULL OR dbo.tblBillingDetail.AuthNo = 0
ISNULL(dbo.tblBillingDetail.AuthNo, 0)
DavidThey are semantically equivalent, though the first one may perform better.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"David" <dlchase@.lifetimeinc.com> wrote in message
news:eV3YFkqGGHA.596@.TK2MSFTNGP10.phx.gbl...
Are the 2 conditions below equivalent? Thanks
dbo.tblBillingDetail.AuthNo IS NULL OR dbo.tblBillingDetail.AuthNo = 0
ISNULL(dbo.tblBillingDetail.AuthNo, 0)
David|||Logically they are, but as far as the optimiser is concerned no. You are
using a function on the column AuthNo so that will negate a s on the
index, you'll get an index scan (possibly) which is like a table scan of the
index.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"David" <dlchase@.lifetimeinc.com> wrote in message
news:eV3YFkqGGHA.596@.TK2MSFTNGP10.phx.gbl...
> Are the 2 conditions below equivalent? Thanks
> dbo.tblBillingDetail.AuthNo IS NULL OR dbo.tblBillingDetail.AuthNo = 0
> ISNULL(dbo.tblBillingDetail.AuthNo, 0)
> David
>|||Almost. :)

> dbo.tblBillingDetail.AuthNo IS NULL OR dbo.tblBillingDetail.AuthNo = 0
ISNULL(dbo.tblBillingDetail.AuthNo, 0) = 0
ML
http://milambda.blogspot.com/

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.