There is a code that I'm trying to improve that looks like this(simple example):

         ,COALESCE(b1.col_c, b2.col_c, b3.col_c)
FROM      tab_a a
LEFT JOIN tab_b b1
          ON a.col_a = b1.col_a
             AND b1.col_b = 'blabla1'
LEFT JOIN tab_b b2
          ON a.col_a = b2.col_a
             AND b2.col_b = 'blabla2'
LEFT JOIN tab_b b3
          ON a.col_a = b3.col_a
             AND b3.col_b = 'blabla3';

You can use the following script to recreate those tables

CREATE TABLE tab_a(col_a int)
CREATE TABLE tab_b(col_a INT, col_b VARCHAR(10), col_c INT)

INSERT INTO dbo.tab_a ( col_a ) VALUES ( 1 ), ( 2 ), ( 3 );

INSERT INTO dbo.tab_b ( col_a
                       ,col_c )
VALUES ( 1, 'blabla1', 1 )
      ,( 1, 'blabla2', 3 )
      ,( 1, 'blabla2', 5 )
      ,( 2, 'blabla2', NULL )
      ,( 2, 'blabla3', 5 );

How can I change it to 1 join + maybe window function and how to rewrite coalesce part. Just to explain, current plan shows 3 tab_b scans, I want to reduce it to 1.

  • What is your current issue?What are you trying to achieve? Please post DDL of underlying tables and also plan on brentozar.com/pastetheplan Nov 14, 2019 at 8:24
  • 1
    If you'll read the query you'll see that it has to scan/seek tab_b 3 times, I want to reduce it to 1 time :-) My actual query is more complicated I just want to understand the idea how to replace 3 joins witn 1 and deal with coalesce part Nov 14, 2019 at 8:29

3 Answers 3

FROM      tab_a a
outer apply (select top 1 b.col_c 
             from tab_b b
             where ((a.col_a = b.col_a
                     AND b.col_b = 'blabla1' )
                  or (a.col_a = b.col_a
                     AND b.col_b = 'blabla2')
                  or (a.col_a = b.col_a
                     AND b.col_b = 'blabla3'))
                  and b.col_c is not null
            order by b.col_b)b;

This solution has 1 tab_b scan but adds sort because you want to choose b.col_c as in your COALESCE. In the example above this order corresponds to the order given by your constants in join condition that correspond to values of c column. In case when the order should be different the thing will be more complicated as you should write customized order byclause.

  • Looks great! The thing is that there are couple columns that I need to return. In this case I need to run outer apply for each column(since the conditions are different). Is there a way to group by result set and use some kind of window function on result set to find Nov 14, 2019 at 12:13
  • >>>The thing is that there are couple columns that I need to return<<< Yesy, one outer apply for every column because not null values of two columns can be situated in different rows
    – sepupic
    Nov 14, 2019 at 12:20
  • 2
    @MichaelCherevko You could post a different question with the extra requirements and a bigger example and expected result Nov 14, 2019 at 12:32
  • Out of interest, why did you choose to write the WHERE filter like that instead of the more compact WHERE a.col_a = b.col_a AND b.col_b IN ('blabla1', 'blabla2', 'blabla3') AND b.col_c IS NOT NULL?
    – Andriy M
    Nov 15, 2019 at 15:21
  • @Andriy M I just copied original filters and pasted them in WHERE clause :)))
    – sepupic
    Nov 15, 2019 at 15:53

If you want or have that 3 descriptions static, you can do the following:

  1. Create a temp table or variable table
  2. Insert the values in the temp or variable table but in form of column https://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server

  3. Write the script in your answer but delete those 3 LEFT JOIN and just do a CROSS JOIN to the temp or variable table

  4. Change the COALESCE code to get the values from the CROSS JOIN object

You can also change your database model to satisfy what you need most.

To help, please share the current database model so we can help you further. You can create it with the following option:

enter image description here

  • Don't sure that I understand how you method will reduce table scan... Added simple tables and insert, can you write the query using those tables? Nov 14, 2019 at 8:51
  • Hi. Yes. I’ll write the query as soon as I arrive to the office.
    – pncsoares
    Nov 14, 2019 at 8:52
  • But have in mind that I can only verify that the number of table scans reduce if you share the database model
    – pncsoares
    Nov 14, 2019 at 9:03
  • What do you mean by "database model"? I'm using 2016 if that is what you asking. Nov 14, 2019 at 9:46
  • 1
    think he wants more info about the tables and relations as you clearly say the example tables are a simplified version of your real database
    – eagle275
    Nov 14, 2019 at 10:01
FROM      tab_a a
LEFT JOIN tab_b b1
          ON a.col_a = b1.col_a
             AND b1.col_b IN ('blabla1','blabla2','blabla3');

1 join as requested ^^ edited to a somewhat more elegant style using IN ( .. )

  • 1
    Check the result of query and compare it from what is required. Moreover table alias name is also incorrect. There is no b2 or b3. Nov 14, 2019 at 9:33
  • Yea -- edited that ... - mind adding that I hate comma-separated values in a single column .. Don't we normalize to reduce that ....
    – eagle275
    Nov 14, 2019 at 9:35
  • Did you check the result set? This is not what I want... Nov 14, 2019 at 9:45
  • I know you want one data-set with denormalized columns from 3 data-sets in table b ... go by pncsoares solution with a view or temp table containing that.
    – eagle275
    Nov 14, 2019 at 9:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.