I want to select all departments along with the name of any employees that happen to be in that department and who earn > 10000. I have written to queries that select the information I want:
SELECT Departments.Name, Employees.Name, Employees.Salary FROM Departments LEFT OUTER JOIN Employees ON Employees.DepartmentId = Departments.Id AND Employees.Salary > 10000;
SELECT Departments.Name, Employees.Name, Employees.Salary FROM Departments LEFT OUTER JOIN ( SELECT * FROM Employees WHERE Salary > 10000 ) AS Employees ON Employees.DepartmentId = Departments.Id;
The actual execution plan for both queries is identical. So in this example I can use either query. My question is whether this will be true for any query. Does SQL Server treat additional criteria in join clauses as if we had created a derived table?
I ask as I am writing code to generate SQL from a graphical design tool and am wondering which style to use. I welcome any thoughts or suggestions you might have.