Joining Tables with Right Function

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

  • 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

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

  • 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
  • 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..

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

  • 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
  • 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.

  • 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.

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

  • SELECT column_name(s)

    FROM table1

    RIGHT OUTER JOIN table2

    ON table1.column_name=table2.column_name;

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

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

  • 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

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

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