Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 201
  • Last Modified:

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

0
Starr Duskk
Asked:
Starr Duskk
  • 4
  • 3
1 Solution
 
Scott PletcherSenior DBACommented:
To best analyze/correct performance, would need to see the definition of "EmployeeUnitJobTypeUnitView" and the current query plan for this code snippet.
0
 
Starr DuskkASP.NET VB.NET DeveloperAuthor Commented:
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
 
SharathData EngineerCommented:
Provide the complete query.
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
Starr DuskkASP.NET VB.NET DeveloperAuthor Commented:
select * from table where otherfieldID NOT IN (select otherfieldID from OtherTable)
0
 
SharathData EngineerCommented:
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
 
Starr DuskkASP.NET VB.NET DeveloperAuthor Commented:
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
 
SharathData EngineerCommented:
SELECT T1.*
FROM Table1 T1
LEFT JOIN Table2 T2
ON T1.JoinColumn = T2.JoinColumn
WHERE T2.JoinColumn IS NULL
0
 
Starr DuskkASP.NET VB.NET DeveloperAuthor Commented:
Excellent! Thanks!
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now