CODEWITHSUNDEEP
sqlsql-serverperformance
JavaSheriff
JavaSheriff

Reputation: 7665

query exist and not exist

I am looking for students with
absence reason_code = 'DOG_ATE_HOME_WORK'
AND that didn't have reason 'I_WAS_DRUNK' in the past

Given the fact that there are thousands of students and millions of reasons in the db, is this the most efficient way to go on mssql?

select student_id  from students where
        exists(
            SELECT 1 FROM absence_reasons absence_r 
            where absence_r.student_id = students.student_id and 
            absence_r.reason_code = 'DOG_ATE_HOME_WORK'
            ) 
        and not
        exists(
            SELECT 1 FROM absence_reasons absence_r 
            where absence_r.student_id = students.student_id 
            and absence_r.reason_code = 'I_WAS_DRUNK'
            )

Upvotes: 1

Views: 58

Answers (2)

KumarHarsh
KumarHarsh

Reputation: 5094

Sorry if it do not work.But this ideal scenario to test other query. Test both along with query execution plan and elapsed time.Also clear the cache from buffer.Also use parameter variable.Do exhausting Testing.

select student_id  from 
            (
            select student_id,reason_code  from students where 
            not exists(
            SELECT student_id1 FROM absence_reasons absence_r 
            where absence_r.student_id = students.student_id 
            and absence_r.reason_code = 'I_WAS_DRUNK'
            )
            )t4
            where reason_code = 'DOG_ATE_HOME_WORK'

Upvotes: 1

Joe Phillips
Joe Phillips

Reputation: 51100

Using EXISTS and NOT EXISTS is typically one of the most efficient ways to do this sort of thing. You just need to make sure you have a student_id index. If you want even more of an improvement, you could add the reason_code to that index.

The only problem is that you've written yours incorrectly.

Your NOT EXISTS query needs to be and absence_r.reason_code = 'I_WAS_DRUNK' instead of and absence_r.reason_code <> 'I_WAS_DRUNK'

Upvotes: 2

Related Questions