- EXCEPT operator must have an equal number of expressions in their target lists.
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.
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 operator can have different number of expressions in their target lists.
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.