SQL Not IN query is taking time with similar table

Question

i have 2 tables suppose table_1 & table_2 table_1 has 56 columns and 1.2 million records my query is like

table_1 like

RollNumber | Subject | G         | Part | Status  
------------------------------------------------  
1          | 1       | 1         | 1    |  1  
1          | 1       | 1         | 2    |  1  
1          | 2       | 1         | 1    |  1  
1          | 2       | 1         | 2    |  5  
1          | 3       | 1         | 1    |  0  
1          | 3       | 1         | 2    |  1  
2          | 1       | 2         | 1    |  1  
2          | 1       | 2         | 2    |  1  
2          | 2       | 2         | 1    |  1  
2          | 2       | 2         | 2    |  1  
2          | 3       | 2         | 1    |  1  
2          | 3       | 2         | 2    |  1 
3          | 1       | 2         | 1    |  1  
3          | 1       | 2         | 2    |  1  
3          | 2       | 2         | 1    |  1  
3          | 2       | 2         | 2    |  1  
3          | 3       | 2         | 1    |  0  
3          | 3       | 2         | 2    |  1

i want all RollNumber (group by with 2nd and third column) from table_1 where any status is 0 but don't want students who also have status = 5(or other than 1)

i have tried this

select * from table_1 as t1  
inner join table_2 as t2  
on  t1.column2 = t2.column2 and t1.column3 = t2.column3 and t1.column4 = t2.column4  
where t1.column1 not in  
     (select column1 from table_1 where status = 5)

This is the inner most query of my qhole query
i have also tried EXCEPT clause
Both queries take too long to execute

Please show the [execution plan](http://stackoverflow.com/questions/7359702/how-do-i-obtain-a-query-execution-plan) of query. Have you added indices to `column1`, `column2`, `column3` and `status`? — kennytm, Feb 23 '17 at 05:15

score 1 · Answer 1 · answered Feb 23 '17 at 05:15

You can use EXISTS in place of NOT IN. This will be faster as there will be a boolean comparison instead of string comparison.

select * from table_1 as t1  
inner join table_2 as t2  
on t1.column1 = t2.column1 and t1.column2 = t2.column2 and t1.column3 = t2.column3  
where not EXISTS  
     (select 1 from table_1 where status = 5 and t1.column3 = table_1.column3)

score 1 · Answer 2 · answered Feb 23 '17 at 05:36

Try to use NOT EXISTS instead of NOT IN

SELECT * 
FROM table_1 AS t1  
INNER JOIN table_2 AS t2  
ON t1.column1 = t2.column1 AND t1.column2 = t2.column2 AND t1.column3 = t2.column3  
WHERE NOT EXISTS(
                    SELECT 1
                    FROM table_1
                    WHERE status=5 AND column3=t1.column3
                                                           )

FuzzyTree · Accepted Answer · 2017-02-23T06:10:07.897

1

Starting with SQL Server 2008 you can use count() over() to count how many total rows in a given group have a certain value.

In this case you'll want to count the number of status <> 1 per group and to only select rows that belong to a group with a count of 0.

select * from (
    select * , 
      count(case when status <> 1 then 1 end) over(partition by RollNumber, G) c
    from table_1
) t where c = 0

edited Feb 23 '17 at 06:10

answered Feb 23 '17 at 05:54

FuzzyTree

32,014
3
54
85

this is working ,,, but issue is i have to convert to LINQ in C#, Thanks by the way – Brainiac Feb 23 '17 at 06:39
If you need help converting the query to LINQ, I suggest opening a new question :) – FuzzyTree Feb 23 '17 at 06:41
i want this query also for mysql – Brainiac Mar 06 '17 at 09:44

SQL Not IN query is taking time with similar table

3 Answers3