EXCEPT

• EXCEPT operator must have an equal number of expressions in their target lists.
  Example :
  

  Select EmpID from #Student
  Except
  Select EmpID from #CourseStudent
  

• Syntax is easy to write.
• It will return distinct rows from left part of EXCEPT if right part of the EXCEPT does not match any rows.

Example

Create table #Student(
EmpID int,
EmpName varchar(100)
)

Create table #CourseStudent
(
  Course Varchar(100),
  EmpID int
)

Insert into #Student(EmpID,EmpName)
Select 1,'Hitesh' Union
Select 2,'Priyanka' Union
Select 3,'Rahul' Union
Select 4,'Abhimanyu' 

Insert into #CourseStudent(Course,EmpID)
Select 'Dot Net',1 Union
Select 'English',2 Union
Select 'Java',3 

//Except query
Select EmpID from #Student
Except
Select EmpID from #CourseStudent

Output : 4
NOT IN

• NOT IN operator can have different number of expressions in their target lists.
  Example :
  

  Select EmpID,EmpName from #Student
  Where EmpID not in(
  Select EmpID from #CourseStudent)
  

• Syntax is some difficult as compare to Except to write.

Output : 4
Note : EXCEPT and NOT IN both have same performance cost.