TSQL optimizing code for NOT IN

Question

I am inheriting an old SQL script that I want to optimize, but after several tests I have to admit that all my tests only create huge SQL with repeating blocks. I would like to know if anyone can suggest a better code for the next template (see Code below). I do not want to use a temporary table (WITH). For simplicity, I add only 3 levels (TMP_C, TMP_D and TMP_E tables), but the source SQL has 8 levels.

WITH TMP_A AS ( SELECT ID, Field_X FROM A TMP_B AS( SELECT DISTINCT ID, Field_Y, CASE WHEN Field_Z IN ('TEST_1','TEST_2') THEN 'CATEG_1' WHEN Field_Z IN ('TEST_3','TEST_4') THEN 'CATEG_2' WHEN Field_Z IN ('TEST_5','TEST_6') THEN 'CATEG_3' ELSE 'CATEG_4' END AS CATEG FROM B INNER JOIN TMP_A ON TMP_A.ID=TMP_B.ID), TMP_C AS ( SELECT DISTINCT ID, CATEG FROM TMP_B WHERE CATEG='CATEG_1'), TMP_D AS ( SELECT DISTINCT ID, CATEG FROM TMP_B WHERE CATEG='CATEG_2' AND ID NOT IN (SELECT ID FROM TMP_C)), TMP_E AS ( SELECT DISTINCT ID, CATEG FROM TMP_B WHERE CATEG='CATEG_3' AND ID NOT IN (SELECT ID FROM TMP_C) AND ID NOT IN (SELECT ID FROM TMP_D)) SELECT * FROM TMP_C UNION SELECT * FROM TMP_D UNION SELECT * FROM TMP_E 

Many thanks for your help.

Answer 1

First, select DISTINCT to exclude duplicates from the result set, so you overload the condition. Adding the definition of “C” and trying to put it into use makes it more confusing to follow. The data ultimately all comes from table "B", where it also has a key match in "A". Let's start with this ... And since you are not using anything from (B) Field_Y or (A) Field_X in your result set, do not add them to the mix of confusion.

 SELECT DISTINCT B.ID, CASE WHEN B.Field_Z IN ('TEST_1','TEST_2') THEN 'CATEG_1' WHEN B.Field_Z IN ('TEST_3','TEST_4') THEN 'CATEG_2' WHEN B.Field_Z IN ('TEST_5','TEST_6') THEN 'CATEG_3' ELSE 'CATEG_4' END AS CATEG FROM B JOIN A ON B.ID = A.ID WHERE B.Field_Z IN ( 'TEST_1', 'TEST_2', 'TEST_3', 'TEST_4', 'TEST_5', 'TEST_6' ) 

The where clause will only include the qualification values ​​of the category that you want, and still have results for each category.

Now, if you really need other values ​​from your Field_Y or Field_X field, then this will create another request. However, your Tmp_C, Tmp_D and Tmp_E only request ID and CATEG columns.

Answer 2

It may work better.

 SELECT DISTINCT B.ID, 'CATEG_1' FROM B JOIN A ON B.ID = A.ID WHERE B.Field_Z IN ( 'TEST_1', 'TEST_2') UNION SELECT DISTINCT B.ID, 'CATEG_2' FROM B JOIN A ON B.ID = A.ID WHERE B.Field_Z IN ( 'TEST_3', 'TEST_4') ...