Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Reworking a NOT IN query

Posted on 2014-03-12
8
Medium Priority
?
199 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:Starr Duskk
  • 4
  • 3
8 Comments
 
LVL 70

Expert Comment

by:Scott Pletcher
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 2

Author Comment

by:Starr Duskk
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 41

Expert Comment

by:Sharath
ID: 39925133
Provide the complete query.
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 2

Author Comment

by:Starr Duskk
ID: 39925240
select * from table where otherfieldID NOT IN (select otherfieldID from OtherTable)
0
 
LVL 41

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 2

Author Comment

by:Starr Duskk
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 41

Accepted Solution

by:
Sharath earned 2000 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 2

Author Closing Comment

by:Starr Duskk
ID: 39927989
Excellent! Thanks!
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
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

972 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