Do you think the below two SQL Queries will yield the same output?
SQL Query-1
Select EmpID, Emp.DeptID , Dept.SubDeptID
FROM TblEmployee Emp
LEFT JOIN TblDept Dept
ON Emp.DeptID = Dept.DeptID
AND Dept.SubDeptID = 'A'
SQL Query-2
Select EmpID, Emp.DeptID , Dept.SubDeptID
FROM TblEmployee Emp
LEFT JOIN TblDept Dept
ON Emp.DeptID = Dept.DeptID
WHERE Dept.SubDeptID = 'A'
The answer is 'No'.
In the first query, the AND condition puts and addition join condition on TblDept.
Which means only those records of TblDept will be considered for a join for which values are SubDeptID = 'A'.
The other records will be ignored meaning there will be NULL as SubDeptID for those Epployee records for whim SubDeptID <> 'A'. Since it is a LEFT JOIN all Employee records will be output.
In the second query, WHERE clause is a filter on the output of the LEFT JOIN between Employee and Dept tables.
First, the LEFT JOIN will be executed, then any records having SubDeptID <> 'A' will be filtered out meaning excluded from final results.
The DDL and DML statements for practice dataset are provided below -
Drop Table TblDept;
Create Table TblDept
(DeptID int,
SubDeptID char(1)) ;
Insert into TblDept VALUES
(1 , 'A'),
(2 , 'A'),
(3 , 'B'),
(4 , 'A');
Create Table TblEmployee
(EmpID int,
DeptID int);
Insert into TblEmployee VALUES
(1001 , 1),
(1002 , 2),
(1003 , 3),
(1004 , 4 )