Solved

Reworking a NOT IN query

Posted on 2014-03-12
8
190 Views
Last Modified: 2014-03-13
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

0
Comment
Question by:BobCSD
  • 4
  • 3
8 Comments
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39924144
To best analyze/correct performance, would need to see the definition of "EmployeeUnitJobTypeUnitView" and the current query plan for this code snippet.
0
 
LVL 1

Author Comment

by:BobCSD
ID: 39924188
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.
0
 
LVL 40

Expert Comment

by:Sharath
ID: 39925133
Provide the complete query.
0
 
LVL 1

Author Comment

by:BobCSD
ID: 39925240
select * from table where otherfieldID NOT IN (select otherfieldID from OtherTable)
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 40

Expert Comment

by:Sharath
ID: 39925643
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.
0
 
LVL 1

Author Comment

by:BobCSD
ID: 39925893
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.
0
 
LVL 40

Accepted Solution

by:
Sharath earned 500 total points
ID: 39926655
SELECT T1.*
FROM Table1 T1
LEFT JOIN Table2 T2
ON T1.JoinColumn = T2.JoinColumn
WHERE T2.JoinColumn IS NULL
0
 
LVL 1

Author Closing Comment

by:BobCSD
ID: 39927989
Excellent! Thanks!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now