Joining Tables with Right Function

Question

Post reply

Joining Tables with Right Function

VegasL

Hall of Fame

Points: 3006
More actions
January 15, 2016 at 9:30 am

#399578

Hello,
I want to join #tbl1 with #tbl2, where the values in #tbl2 should match #tbl1. The problem is in #tbl1 I have fixed amount of characters value to the right most. How can I join these?
See example:
create table #tb_1 (SKU1 varchar(100))
create table #tb_2 (SKU2 varchar(100))
insert into #tb_1
(SKU1) values ('ABC123A-PO');
insert into #tb_1
(SKU1) values ('ABC123B-PO');
insert into #tb_1
(SKU1) values ('ABC123C-PO');
insert into #tb_2
(SKU2) values ('ABC123A');
insert into #tb_2
(SKU2) values ('ABC123B');
insert into #tb_2
(SKU2) values ('ABC123C');
I want it to return, the matching values (which both columns should match), I hence tried below.
select #tb_1.[SKU1],#tb_2.[SKU2]
from #tb_1
join #tb_2 on [#tb_1].SKU1=[#tb_2].SKU2
where (right([#tb_1],3)

Viewing 15 posts - 1 through 15 (of 21 total)

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

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 1

possibly ??

SELECT tb_1.SKU1,

tb_2.SKU2

FROM tb_1

INNER JOIN tb_2 ON LEFT(tb_1.SKU1, CHARINDEX('-', tb_1.SKU1)-1) = tb_2.SKU2;

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

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

VegasL (1/15/2016)
Hello,
I want to join #tbl1 with #tbl2, where the values in #tbl2 should match #tbl1. The problem is in #tbl1 I have fixed amount of characters value to the right most. How can I join these?
See example:
create table #tb_1 (SKU1 varchar(100))
create table #tb_2 (SKU2 varchar(100))
insert into #tb_1
(SKU1) values ('ABC123A-PO');
insert into #tb_1
(SKU1) values ('ABC123B-PO');
insert into #tb_1
(SKU1) values ('ABC123C-PO');
insert into #tb_2
(SKU2) values ('ABC123A');
insert into #tb_2
(SKU2) values ('ABC123B');
insert into #tb_2
(SKU2) values ('ABC123C');
I want it to return, the matching values (which both columns should match), I hence tried below.
select #tb_1.[SKU1],#tb_2.[SKU2]
from #tb_1
join #tb_2 on [#tb_1].SKU1=[#tb_2].SKU2
where (right([#tb_1],3)

Having either RIGHT or LEFT (or any function) on the columns in selection or join criteria pretty much guarantees a scan. Use the formula in a persisted, indexed, computed column and join on that, instead. Better yet, normalize your data so that you don't have to do this all the time.

--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)

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

Another option.

SELECT *

FROM #tb_1 t1

JOIN #tb_2 t2 ON t1.SKU1 LIKE t2.SKU2 + '-__'

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

VegasL Hall of Fame Points: 3006 More actions · Answer 4

Thanks Luis, the last one appears straightforward, concatenation with fixed characters. However when I try on actual table without the # temp tables, i get incorrect syntax near the keyword 'like'.

Any clue as to what may be causing that? nothing else changed..

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

VegasL (1/15/2016)
Thanks Luis, the last one appears straightforward, concatenation with fixed characters. However when I try on actual table without the # temp tables, i get incorrect syntax near the keyword 'like'.
Any clue as to what may be causing that? nothing else changed..

Not possible to tell without seeing the actual query you're using. At best, it would only be a guess without seeing the query.

Also, such concatenation is still going to cause scans.

--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)

Luis Cazares SSC Guru Points: 183706 More actions · Answer 6

Some options:

- You're missing an alias,

- You have a space where you shouldn't,

- You're missing a comma,

- If you're using dynamic code, the single quotes aren't escaped,

- a lot more reasons.

Hard to say without being able to see the code.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

VegasL Hall of Fame Points: 3006 More actions · Answer 7

Thank You J Livingston..you're code worked, Also thank you Luis while you're code worked I need to see what I am overlooking on actual table.

Ed Wagner SSC Guru Points: 287026 More actions · Answer 8

Did you read what Jeff posted? Using a function on a join predicate is going to result in a scan, which is very inefficient. The function has to be calculated on every row in the table before the join can be performed. This means that if you have a 1M row table, you need to perform that string function 1M times before the join even happens.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

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

Ed Wagner (1/15/2016)
Did you read what Jeff posted? Using a function on a join predicate is going to result in a scan, which is very inefficient. The function has to be calculated on every row in the table before the join can be performed. This means that if you have a 1M row table, you need to perform that string function 1M times before the join even happens.

Heh... no one listens until they have an actual performance problem when they can least afford it. 🙂 I've got a ton of non-SARGable code at work that has been the bane of performance that I've been fixing as the problems rear their ugly heads. As you know, it's a lot more difficult to do after it becomes a problem than before. The only good part about doing it after is that you don't have to look for the problems... they find you. 😛

--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)

johnwalker10 SSCrazy Eights Points: 9124 More actions · Answer 10

SELECT column_name(s)

FROM table1

RIGHT OUTER JOIN table2

ON table1.column_name=table2.column_name;

Luis Cazares SSC Guru Points: 183706 More actions · Answer 11

johnwalker10 (1/17/2016)
SELECT column_name(s) FROM table1 RIGHT OUTER JOIN table2
ON table1.column_name=table2.column_name;

Try to read the question before posting and, if possible, the previous posts. Also test code against sample data and compare it to expected results when they're provided.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 12

VegasL (1/15/2016)
Thanks Luis, the last one appears straightforward, concatenation with fixed characters. However when I try on actual table without the # temp tables, i get incorrect syntax near the keyword 'like'.
Any clue as to what may be causing that? nothing else changed..

Luis' query is SARGable. If there's an index on #tb_1.SKU1 then you'll get a seek + range scan

Seek Keys[1]: Start: [tempdb].[dbo].[#tb_1].SKU1 > Scalar Operator([Expr1008]), End: [tempdb].[dbo].[#tb_1].SKU1 < Scalar Operator([Expr1009])

so worth pursuing further.

Please post your code.

^{“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

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 13

ChrisM@Work (1/18/2016)
VegasL (1/15/2016)
Thanks Luis, the last one appears straightforward, concatenation with fixed characters. However when I try on actual table without the # temp tables, i get incorrect syntax near the keyword 'like'.
Any clue as to what may be causing that? nothing else changed..
Luis' query is SARGable. If there's an index on #tb_1.SKU1 then you'll get a seek + range scan
Seek Keys[1]: Start: [tempdb].[dbo].[#tb_1].SKU1 > Scalar Operator([Expr1008]), End: [tempdb].[dbo].[#tb_1].SKU1 < Scalar Operator([Expr1009])
so worth pursuing further.
Please post your code.

Are you talking about this one, Chris?

SELECT *

FROM #tb_1 t1

JOIN #tb_2 t2 ON t1.SKU1 LIKE t2.SKU2 + '-__'

Just a thought, Chris... I agree that it can do a seek on #tb_1 but I don't believe it's possible to do a seek on #tb_2 here because of the concatenation that must be done. I haven't tried it but I'm pretty sure that will cause scans on #tb_2 even though the LIKE is a non-leading-wildcard LIKE. I'm not sure that I would call the query SARGable because of that.

--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)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 14

Jeff Moden (1/18/2016)[hrCan we agree on "SARGable in one direction only" as it's often sufficient to permit tweaking a decent plan?

With the battles I'm currently facing at work on this identical subject on some rather large tables and heavily used similar code, my most politically correct answer would have to be "Oh hell no!". 😀

^{“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