sql server - TSQL optimizing code for NOT IN -

i inherit old sql script want optimize after several tests, must admit tests creates huge sql repetitive blocks. know if can propose better code following pattern (see code below). don't want use temporary table (with). simplicity, put 3 levels (table tmp_c, tmp_d , tmp_e) original sql have 8 levels.

with tmp_a ( select  id,  field_x  tmp_b  as( select distinct  id,  field_y,  case   when field_z in ('test_1','test_2') 'categ_1'   when field_z in ('test_3','test_4') 'categ_2'   when field_z in ('test_5','test_6') 'categ_3'   else 'categ_4'  end categ b inner join tmp_a on tmp_a.id=tmp_b.id),  tmp_c ( select distinct   id,  categ tmp_b categ='categ_1'),  tmp_d ( select distinct   id,  categ tmp_b categ='categ_2' , id not in (select id tmp_c)),  tmp_e ( select distinct  id,  categ tmp_b categ='categ_3'  , id not in (select id tmp_c)  , id not in (select id tmp_d))  select * tmp_c union select * tmp_d union select * tmp_e 

many in advance help.

first off, select distinct prevent duplicates result set, overworking condition. adding "with" definitions , trying nest use makes more confusing follow. data coming "b" table has key match in "a". lets start that... , since not using (b)field_y or (a)field_x in result set, don't add them mix of confusion.

select distinct       b.id,       case when b.field_z in ('test_1','test_2') 'categ_1'            when b.field_z in ('test_3','test_4') 'categ_2'            when b.field_z in ('test_5','test_6') 'categ_3'            else 'categ_4'            end categ          b  join    on  b.id = a.id          b.field_z in ( 'test_1', 'test_2', 'test_3', 'test_4', 'test_5', 'test_6' ) 

the clause include category qualifying values want , still have results per each category.

now, if needed other values "field_y" or "field_x", generate different query. however, tmp_c, tmp_d , tmp_e asking id , categ columns anyhow.