diff in on clause and where clause????? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

diff in on clause and where clause?????

Author

Topic

papillon2811
Starting Member

18 Posts

Posted - 2007-04-04 : 01:43:41

hi..
i have basic question like

what is differance between conditions put in ON clause and in WHERE clause in JOINS????

see conditions that shown in brown color

select d1.SourceID, d1.PID, d1.SummaryID, d1.EffectiveDate,
d1.Audit, d1.ExpirationDate, d1.Indicator
from [DB1].[dbo].[Implicit] d1 inner join [DB2].[dbo].[Implicit] d2
on d1.SummaryID=d2.SummaryID
AND d1.ListType = d2.ListType
AND (d1.EffectiveDate <= d2.ExpirationDate or d2.ExpirationDate is null)
AND (d1.ExpirationDate >= d2.EffectiveDate or d1.ExpirationDate is null)
where d1.ImplicitID >= d2.ImplicitID AND
(d1.SourceID<>d2.SourceID
OR (d1.SourceID IS NULL AND d2.SourceID IS NOT NULL)
OR (d1.SourceID IS NOT NULL AND d2.SourceID IS NULL)
)

select d1.SourceID, d1.PID, d1.SummaryID, d1.EffectiveDate,
d1.Audit, d1.ExpirationDate, d1.Indicator
from [DB1].[dbo].[Implicit] d1 inner join [DB2].[dbo].[Implicit] d2
on d1.SummaryID=d2.SummaryID
AND d1.ImplicitID = d1.ImplicitID AND d1.ListType = d2.ListType
AND (d1.EffectiveDate <= d2.ExpirationDate or d2.ExpirationDate is null)
AND (d1.ExpirationDate >= d2.EffectiveDate or d1.ExpirationDate is null)
where d1.ImplicitID >= d2.ImplicitID AND
(d1.SourceID<>d2.SourceID
OR (d1.SourceID IS NULL AND d2.SourceID IS NOT NULL)
OR (d1.SourceID IS NOT NULL AND d2.SourceID IS NULL)
)

another thing...

if we put AND d1.ImplicitID = d1.ImplicitID condition in second query then shall we remove
d1.ImplicitID >= d2.ImplicitID from WHERE clause????

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-04 : 01:47:48

"what is differance between conditions put in ON clause and in WHERE clause in JOINS????"
It makes no different for INNER JOIN BUT not for OUTER JOIN.

"if we put AND d1.ImplicitID = d1.ImplicitID condition in second query then shall we remove
d1.ImplicitID >= d2.ImplicitID from WHERE clause????"
one is EQUAL one is GREATER AND EQUAL. It is not the same.

KH

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-04 : 01:49:14

When using INNER JOIN, there is no measurable difference.
On a LEFT JOIN, the additional ON filter, filters the joined table directly and when putting it in the WHERE clause, filters the whole resultset.
This mostly has to do with how to handle NULLs.

Peter Larsson
Helsingborg, Sweden

papillon2811
Starting Member

18 Posts

Posted - 2007-04-04 : 01:51:05

"if we put AND d1.ImplicitID = d1.ImplicitID condition in second query then shall we remove
d1.ImplicitID >= d2.ImplicitID from WHERE clause????"
one is EQUAL one is GREATER AND EQUAL. It is not the same.

I mean to say output of these two queries are same or not????

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-04 : 02:04:36

quote:
Originally posted by papillon2811

"if we put AND d1.ImplicitID = d1.ImplicitID condition in second query then shall we remove
d1.ImplicitID >= d2.ImplicitID from WHERE clause????"
one is EQUAL one is GREATER AND EQUAL. It is not the same.

I mean to say output of these two queries are same or not????

It might not be the same. As the LOGIC is clearly different.

d1.ImplicitID = d1.ImplicitID

is DIFFERENT from

d1.ImplicitID >= d2.ImplicitID

For you query, as it is only INNER JOIN, the result will be the same if you removed the d1.ImplicitID >= d1.ImplicitID and place it in the ON.

KH

papillon2811
Starting Member

18 Posts

Posted - 2007-04-04 : 02:10:58

thanx :)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-04 : 02:12:01

these 2 are the same


select	d1.SourceID, d1.PID, d1.SummaryID, d1.EffectiveDate,
	d1.Audit, d1.ExpirationDate, d1.Indicator
from 		[DB1].[dbo].[Implicit] d1 
inner join 	[DB2].[dbo].[Implicit] d2
	on 	d1.SummaryID		= d2.SummaryID
	AND 	d1.ListType 		= d2.ListType
	AND 	(d1.EffectiveDate 	<= d2.ExpirationDate 	or d2.ExpirationDate is null)
	AND 	(d1.ExpirationDate 	>= d2.EffectiveDate 	or d1.ExpirationDate is null)
where	d1.ImplicitID >= d2.ImplicitID		AND
	(
		d1.SourceID<>d2.SourceID
	OR 	(d1.SourceID IS NULL 		AND d2.SourceID IS NOT NULL)
	OR 	(d1.SourceID IS NOT NULL 	AND d2.SourceID IS NULL)
	)

select	d1.SourceID, d1.PID, d1.SummaryID, d1.EffectiveDate,
	d1.Audit, d1.ExpirationDate, d1.Indicator
from 		[DB1].[dbo].[Implicit] d1 
inner join 	[DB2].[dbo].[Implicit] d2
	on 	d1.SummaryID		= d2.SummaryID
	ANS	d1.ImplicitID 		>= d2.ImplicitID
	AND 	d1.ListType 		= d2.ListType
	AND 	(d1.EffectiveDate 	<= d2.ExpirationDate 	or d2.ExpirationDate is null)
	AND 	(d1.ExpirationDate 	>= d2.EffectiveDate 	or d1.ExpirationDate is null)
where	d1.ImplicitID >= d2.ImplicitID		AND
	(
		d1.SourceID<>d2.SourceID
	OR 	(d1.SourceID IS NULL 		AND d2.SourceID IS NOT NULL)
	OR 	(d1.SourceID IS NOT NULL 	AND d2.SourceID IS NULL)
	)

KH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources