Understanding the difference between Join and Where filters

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Understanding the difference between Join and...

15 posts, Page 2 of 2

««1 2

Understanding the difference between Join and Where filters - SQL 2008R2

Rate Topic

Display Mode

Topic Options

Author

Message

Steven Willis

Posted Tuesday, September 25, 2012 9:58 AM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 7:19 AM
Points: 283, Visits: 1,239

Here's another option that moves the string manipulation and identification of NULLs to the SELECT statement instead of using it in a JOIN or WHERE clause where the 'SARG-ability' of the statement may be an issue. It sets blanks and NULLs to some value (in this example it's '0') and then the Where clause can exclude that value. This option would need to be tested against other options of course.


SELECT
    [Desc]
   ,[Num]
   ,[Batch]
   ,[CodeId]
   ,[Type]
   ,[Id]
FROM
	(
	SELECT DISTINCT
		CD.[Desc] AS [Desc]
	   ,SD.[Num] AS [Num]
	   ,SD.[Batch] AS [Batch]
	   ,CD.[CodeId] AS [CodeId]
	   ,SD.[Type] AS [Type]	
	   ,CH.[Id] AS [Id]
	   ,ISNULL(NULLIF(RTRIM(LTRIM(SD.[Code])),''),'0') AS [Code]  
	FROM
		Table1 SD
	JOIN 
		Table2 CH
		ON CH.Num = SD.Num
		   AND CH.Batch = @Batch
		   AND CH.Flag = 0
		   AND SD.Batch = @Batch
		   AND SD.Flag = 0
	JOIN 
		Table3 M
		ON M.Code = SD.Code
		   AND (Map = 1
				OR Map = @Map)
		   AND CH.Date1 BETWEEN M.Date2 AND M.Date3
	JOIN 
		Table4 CD
		ON M.CodeId = CD.CodeId
	) AS Result
WHERE
    [Code] <> '0'

Post #1364154

tim_harkin

Posted Tuesday, September 25, 2012 2:53 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 6:26 AM
Points: 138, Visits: 356

GilaMonster (9/24/2012)

laurie-789651 (9/24/2012)

2. You could consider using

FROM Table1 SD WITH (NOLOCK) JOIN Table2 WITH (NOLOCK) etc.

as long as the table isn't being updated - this saves time as no read locks are issued.

And incorrect results are possible, not just dirty reads, duplicate or missing rows. If there are no changes being made, there's little gain from nolock, the overhead of taking locks is not that high, and it introduces the potential for incorrect results when changes are being made.

See - http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx

A quick little demo to show incorrect results from NOLOCK, even when the data you are reading is static.

http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/

Post #1364293

ChrisM@Work

Posted Wednesday, September 26, 2012 1:43 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:27 AM
Points: 5,618, Visits: 10,990

Steven Willis (9/25/2012)


SELECT
    [Desc]
   ,[Num]
   ,[Batch]
   ,[CodeId]
   ,[Type]
   ,[Id]
FROM
	(
	SELECT DISTINCT
		CD.[Desc] AS [Desc]
	   ,SD.[Num] AS [Num]
	   ,SD.[Batch] AS [Batch]
	   ,CD.[CodeId] AS [CodeId]
	   ,SD.[Type] AS [Type]	
	   ,CH.[Id] AS [Id]
	   ,ISNULL(NULLIF(RTRIM(LTRIM(SD.[Code])),''),'0') AS [Code]  
	FROM
		Table1 SD
	JOIN 
		Table2 CH
		ON CH.Num = SD.Num
		   AND CH.Batch = @Batch
		   AND CH.Flag = 0
		   AND SD.Batch = @Batch
		   AND SD.Flag = 0
	JOIN 
		Table3 M
		ON M.Code = SD.Code
		   AND (Map = 1
				OR Map = @Map)
		   AND CH.Date1 BETWEEN M.Date2 AND M.Date3
	JOIN 
		Table4 CD
		ON M.CodeId = CD.CodeId
	) AS Result
WHERE
    [Code] <> '0'

There's nothing wrong with your reasoning, Steven - it looks like a winner. However, I strongly suspect that the execution plan of your query would be identical to this:

	SELECT DISTINCT
		CD.[Desc] AS [Desc]
	   ,SD.[Num] AS [Num]
	   ,SD.[Batch] AS [Batch]
	   ,CD.[CodeId] AS [CodeId]
	   ,SD.[Type] AS [Type]	
	   ,CH.[Id] AS [Id]
	   ,ISNULL(NULLIF(RTRIM(LTRIM(SD.[Code])),''),'0') AS [Code]  
	FROM
		Table1 SD
	JOIN 
		Table2 CH
		ON CH.Num = SD.Num
		   AND CH.Batch = @Batch
		   AND CH.Flag = 0
		   AND SD.Batch = @Batch
		   AND SD.Flag = 0
	JOIN 
		Table3 M
		ON M.Code = SD.Code
		   AND (Map = 1
				OR Map = @Map)
		   AND CH.Date1 BETWEEN M.Date2 AND M.Date3
	JOIN 
		Table4 CD
		ON M.CodeId = CD.CodeId
WHERE
    ISNULL(NULLIF(RTRIM(LTRIM(SD.[Code])),''),'0') <> '0'

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps

Post #1364486

GilaMonster

Posted Wednesday, September 26, 2012 1:52 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:03 AM
Points: 37,735, Visits: 30,004

Yes, those two will be identical.

While the string manipulation's in the select, that derived column is used in the where, so it's absolutely the same as having the string manipulation in the where.

Both of these, for example, are not SARGable and will simplify to the same query structure.

SELECT <columns> FROM SomeTable
WHERE Substring(SomeColumn,2,3) = 'abc'

SELECT * FROM (
   SELECT <columns>, Substring(SomeColumn,2,3)  as TrimmedString FROM SomeTable
) sub
WHERE sub.TrimmedString = 'abc'

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1364491

Steven Willis

Posted Wednesday, September 26, 2012 8:39 AM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 7:19 AM
Points: 283, Visits: 1,239

GilaMonster (9/26/2012)

SELECT <columns> FROM SomeTable
WHERE Substring(SomeColumn,2,3) = 'abc'

SELECT * FROM (
   SELECT <columns>, Substring(SomeColumn,2,3)  as TrimmedString FROM SomeTable
) sub
WHERE sub.TrimmedString = 'abc'

Thanks for that explanation.

Just thinking out loud...could there ever be a benefit to having a calculated column that converts any nulls to something non-null for queries/indexes? I suppose the best solution is to not allow nulls but I import lots of data where I'm stuck with what I get. Would it be better to convert the data to a blank or some other value during insert/update even if the value is truly an unknown? What other options are there for avoiding an IsNull conversion or a 'WHERE col = val [or/and] col [is/is not] null"...or is that even something to avoid?

Post #1364745

« Prev Topic | Next Topic »

15 posts, Page 2 of 2

««1 2

Permissions