There is a code that I'm trying to improve that looks like this(simple example):
SELECT DISTINCT a.col_a
,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_b
,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.
Best Answer
This solution has 1 tab_b
scan
but addssort
because you want to chooseb.col_c
as in yourCOALESCE
. In the example above thisorder
corresponds to theorder
given by yourconstants
injoin condition
that correspond to values ofc
column. In case when the order should be different the thing will be more complicated as you should write customizedorder by
clause.