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.