Complex Join

Question

Complex Join

Viewing 12 posts - 16 through 26 (of 26 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 101098 More actions · Answer 1

Jeff Moden wrote:

Sergiy wrote:
Still there is a shorter version:
select * 
from leftTable  T1
join rightTable T2 
   on (
      T1.C1 = T2.C1 AND (ISNULL(T1.C2, T2.C2) = ISNULL(T2.C2, T1.C2))
      )
   OR (
      T2.C2 = T1.C2 AND (ISNULL(T1.C1, T2.C1) = ISNULL(T2.C1, T1.C1))
      )
Whoo-hoo!
🙂
I came up with something similar prior to my previous post and rejected it as a solution on my part because of the ISNULL's. It would be interesting to see if the original code or this codes fairs better on a large, properly indexed pair of tables.

It could potentially fare worse, in the future even if not now.

Again, one should never use ISNULL in a WHERE or JOIN.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 2

ScottPletcher wrote:

Jeff Moden wrote:
Sergiy wrote:
Still there is a shorter version:
select * 
from leftTable  T1
join rightTable T2 
   on (
      T1.C1 = T2.C1 AND (ISNULL(T1.C2, T2.C2) = ISNULL(T2.C2, T1.C2))
      )
   OR (
      T2.C2 = T1.C2 AND (ISNULL(T1.C1, T2.C1) = ISNULL(T2.C1, T1.C1))
      )
Whoo-hoo!
🙂
I came up with something similar prior to my previous post and rejected it as a solution on my part because of the ISNULL's. It would be interesting to see if the original code or this codes fairs better on a large, properly indexed pair of tables.
It could potentially fare worse, in the future even if not now.
Again, one should never use ISNULL in a WHERE or JOIN.

NEVER is the wrong word here in the context you've used it. It's quite acceptable to use just about any function you want on a table column that MUST be scanned anyway or on a variable in the WHERE or JOIN.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sergiy SSC Guru Points: 110208 More actions · Answer 3

Jeff Moden wrote:

I came up with something similar prior to my previous post and rejected it as a solution on my part because of the ISNULL's. It would be interesting to see if the original code or this codes fairs better on a large, properly indexed pair of tables.

I totally agree with your concern.

If C1 and C2 are properly indexed and the cardinality of both initialised values and NULL's is high then the "long way" would be way faster.

Only if it's "Clustered Index Scan" followed by "Hash Join" in the execution plan anyway then reducing the number of scans would be beneficial.

This reply was modified 4 years, 2 months ago by Sergiy.

_____________
Code for TallyGenerator

ScottPletcher SSC Guru Points: 101098 More actions · Answer 4

Jeff Moden wrote:

ScottPletcher wrote:
Jeff Moden wrote:
Sergiy wrote:
Still there is a shorter version:
select * 
from leftTable  T1
join rightTable T2 
   on (
      T1.C1 = T2.C1 AND (ISNULL(T1.C2, T2.C2) = ISNULL(T2.C2, T1.C2))
      )
   OR (
      T2.C2 = T1.C2 AND (ISNULL(T1.C1, T2.C1) = ISNULL(T2.C1, T1.C1))
      )
Whoo-hoo!
🙂
I came up with something similar prior to my previous post and rejected it as a solution on my part because of the ISNULL's. It would be interesting to see if the original code or this codes fairs better on a large, properly indexed pair of tables.
It could potentially fare worse, in the future even if not now.
Again, one should never use ISNULL in a WHERE or JOIN.
NEVER is the wrong word here in the context you've used it. It's quite acceptable to use just about any function you want on a table column that MUST be scanned anyway or on a variable in the WHERE or JOIN.

I left off the earlier-stated "ISNULL on a table column" because I took it to be implied here. Yes, ISNULL is always fine on variables.

I'm still opposed to it on "MUST be scanned" because we have no idea what the optimizer might be capable of doing next year or the year after that, or because someone might adjust the query and/or add an index in the next week in such a way that SQL could avoid a scan. Therefore, I'll stick with never use ISNULL on a table column in a WHERE (or JOIN) clause because it could lead to loss of performance.

I understand there are very few ALWAYS / NEVER rules, but I do think this is one of the few.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 5

While I usually and strongly agree that ISNULL shouldn't be used on table columns in a Join, Where, or other column based criteria matching, "It Depends" is always a consideration in everything. What kind of a change do you think they could possibly make that would do the equivalent of an ISNULL on a column in a Temporary Table that contains only and all the rows that you need to look at? What would you do that would accomplish the same thing without making it slower?

In this case, I'll agree to "Almost always not" but not "Never". 😀 That's how "bad" supposed "Best Practices" are created.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 101098 More actions · Answer 6

Jeff Moden wrote:

While I usually and strongly agree that ISNULL shouldn't be used on table columns in a Join, Where, or other column based criteria matching, "It Depends" is always a consideration in everything. What kind of a change do you think they could possibly make that would do the equivalent of an ISNULL on a column in a Temporary Table that contains only and all the rows that you need to look at? What would you do that would accomplish the same thing without making it slower?
In this case, I'll agree to "Almost always not" but not "Never". 😀 That's how "bad" supposed "Best Practices" are created.

The problem is that if it's allowed in some places, it will get copied to others, spreading a very bad practice that is (almost) never required.

What kind of a change ...? How about ending up needing to look at only some of the rows for a particular query?

What would you do ...? ISNULL should never be faster, only potentially slower.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 7

ScottPletcher wrote:

Jeff Moden wrote:
While I usually and strongly agree that ISNULL shouldn't be used on table columns in a Join, Where, or other column based criteria matching, "It Depends" is always a consideration in everything. What kind of a change do you think they could possibly make that would do the equivalent of an ISNULL on a column in a Temporary Table that contains only and all the rows that you need to look at? What would you do that would accomplish the same thing without making it slower?
In this case, I'll agree to "Almost always not" but not "Never". 😀 That's how "bad" supposed "Best Practices" are created.
The problem is that if it's allowed in some places, it will get copied to others, spreading a very bad practice.
What kind of change? How about ending up needing to look at only some of the rows for a particular query?
ISNULL should never be faster, only potentially slower.

Totally understood on the "it will get copied to others" thing. That's why when I do find an exception, I add a comment to the code.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 101098 More actions · Answer 8

Jeff Moden wrote:

ScottPletcher wrote:
Jeff Moden wrote:
While I usually and strongly agree that ISNULL shouldn't be used on table columns in a Join, Where, or other column based criteria matching, "It Depends" is always a consideration in everything. What kind of a change do you think they could possibly make that would do the equivalent of an ISNULL on a column in a Temporary Table that contains only and all the rows that you need to look at? What would you do that would accomplish the same thing without making it slower?
In this case, I'll agree to "Almost always not" but not "Never". 😀 That's how "bad" supposed "Best Practices" are created.
The problem is that if it's allowed in some places, it will get copied to others, spreading a very bad practice.
What kind of change? How about ending up needing to look at only some of the rows for a particular query?
ISNULL should never be faster, only potentially slower.
Totally understood on the "it will get copied to others" thing. That's why when I do find an exception, I add a comment to the code.

I can't figure out all why you would want to encourage the terrible practice of using ISNULL -- and presumably then CONVERT and other functions -- in a WHERE clause, even if you're confirmed for one query that a scan is required.

I don't see any reason not to just properly code an IS NULL check instead of insisting on using ISNULL.

Of course you have your right to stick to using ISNULL, just don't be suprised when it gets copied to other places where it's far less suitable. Many people often don't copy comments. And, if forced to work on that SQL statement, I'd still remove the use of ISNULL, no matter what the comments stated.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 9

ScottPletcher wrote:

And, if forced to work on that SQL statement, I'd still remove the use of ISNULL, no matter what the comments stated.

So like I asked before... what would you replace it with that wouldn't slow the code down knowing that, you too, would have to use the entire table?

The other thing is that I went for years without using an ISNULL because of the rarity of when it's OK. Guess what? No one copied my code but the place is rampant with it's use. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 101098 More actions · Answer 10

Jeff Moden wrote:

ScottPletcher wrote:
And, if forced to work on that SQL statement, I'd still remove the use of ISNULL, no matter what the comments stated.
So like I asked before... what would you replace it with that wouldn't slow the code down knowing that, you too, would have to use the entire table?
The other thing is that I went for years without using an ISNULL because of the rarity of when it's OK. Guess what? No one copied my code but the place is rampant with it's use. 😉

And I answer again: it should NOT slow down the code, so I don't see the point of the q. ISNULL cannot not speed up code, afaik.

They can simply copy the technique rather than the code itself. They see someone very skilled code an ISNULL in WHERE, then it must be ok to code ISNULLs in WHEREs, right?

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 11

I'm not talking about ISNULL speeding anything up, Scott. I'm talking about the use of a Clustered Index slowing things down. I'll try to come up with an example other than the one that I believe I pointed out in Hierarchies on Steroids #1.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 101098 More actions · Answer 12

Sorry, I had some difficulty figuring out specifically what you meant.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".