asked on

Reworking a NOT IN query

Can you advise on how to rework this section? using a JOIN or the best method for tuning? thanks!

AND 
 ( 
TrainingCourse.TrainingCourseId IN (Select TrainingCourseId from TrainingCourseUnit WHERE UnitId IN (Select UnitId from EmployeeUnitJobTypeUnitView where EmployeeId = Employee.EmployeeId))
 OR 
TrainingCourse.TrainingCourseId NOT IN (Select TrainingCourseId from TrainingCourseUnit)
 )

Open in new window

Scott Pletcher

To best analyze/correct performance, would need to see the definition of "EmployeeUnitJobTypeUnitView" and the current query plan for this code snippet.

Starr Duskk

ASKER

Can you just give me a generalization on if I'm using a

where NOT IN (select....

you suggest something better....

like instead of using a

where IN (select

I'd use a join clause.

thanks.

Sharath S

Provide the complete query.

Starr Duskk

ASKER

select * from table where otherfieldID NOT IN (select otherfieldID from OtherTable)

Sharath S

I don't think you provided the complete query. There is Employee in the WHERE condition. Without knowing the full query, its tough to reconstruct.

Starr Duskk

ASKER

Okay, PLEASE FORGET my first example.

All I want to know is another option for a NOT IN.

Can anyone please give me some examples based on this simple little query?

select * from table where otherfieldID NOT IN (select otherfieldID from OtherTable)

thanks.

ASKER CERTIFIED SOLUTION

Sharath S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Starr Duskk

ASKER

Excellent! Thanks!