What is the difference between EXCEPT and NOT IN in SQL Server?


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.

2 thoughts on “What is the difference between EXCEPT and NOT IN in SQL Server?

Comments are closed.