Use left join in place of Sub query “NOT IN” in SQL Server | Part 18
Here we will learn how to use left join in place of Sub query “NOT IN”,
Here we first understand what is not clause in sub query, let us see:
NOT IN: SQL statement selects all empid that are NOT listed in employee_salary_detail table
employees table 1
empid Name salary
101 Breylin 20000
102 Breylin A 20000
103 Breylin B 20000
104 Ame 3000
employee_salary_detail table 2
id empid salary_month
1 101 20-Jan
2 102 20-Jan
SQL Statement :
select * from employees where empid not in (select empid from employee_salary_detail)
Also, we can achieve it through left join, let us understand what is left join:
Left outer join produces a complete set of records from Table A, with the matching records (where available) in Table B. If there is no match, the right side will contain null.
select * from employees emp left join employee_salary_detail emp_det on emp.empid=emp_det.empid
using this you can see the below output:
employees table 1 employee_salary_detail table 2
empid Name salary id empid salary_month
101 Breylin 20000 1 101 20-Jan
102 Breylin A 20000 2 102 20-Jan
103 Breylin B 20000 NULL NULL NULL
104 Ame 3000 NULL NULL NULL
So, when you use emp.det is null , you can get the final results. And final query will be:
select * from employees emp
left join employee_salary_detail emp_det
on emp.empid=emp_det.empid
where emp_det.empid is null