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.
asked Jan 05, 2010 at 10:22 AM in Default
The answer is, it depends. The criteria you're showing here are simple enough and the joins are straight forward, so they're operating in the same fashion. However, outer joins like you're defining with filtering criteria in the ON clause basically function like inner joins. Try changing the query to put the filter into the WHERE clause and see if the data changes.
In general, I would not use derived tables if I can define the joins without them. I would only use them when I can't define the criteria any other way.
answered Jan 05, 2010 at 10:47 AM
Grant Fritchey ♦♦
Posting an answer instead of a comment because the comment wouldn't let me put in the code I wanted to include.
Doesn't adding the Criteria on the RIGHT table in the WHERE clause convert it a LEFT OUTER JOIN to an INNER JOIN while adding it to the ON clause KEEPS THE OUTER JOIN? Try this example:
The Results and the Execution plan shows the first query with the criteria in the ON clause stays a LEFT OUTER JOIN while the second query converts to an INNER JOIN.
Other than that I agree with Grant, especially the part about avoiding derived tables when you can.
answered Jan 05, 2010 at 12:12 PM