question

KG avatar image
KG asked

Is it better to use a derived table or join criteria?

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.

joins
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

2 Answers

·
Grant Fritchey avatar image
Grant Fritchey answered

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.

2 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield avatar image Matt Whitfield ♦♦ commented ·
+1 - spot on answer, and includes 'it depends'. I'd +2 if I could :)
1 Like 1 ·
TimothyAWiseman avatar image TimothyAWiseman commented ·
I'll add the second +1 since Matt can't ;-) In most testing I have done with fairly simple queries like this the query plans will be the same if the queries are equivalent. With truly complicated queries that could easily change. Unless there is a real performance difference, I try to focus on readability and maintainability in writing queries. Even for ad hoc queries this makes it less likely I made a mistake, and for long lived code it can be vital. Avoiding a derived table when possible normally makes it easier to read, and using a CTE can help readability when you must use them.
1 Like 1 ·
Jack Corbett avatar image
Jack Corbett answered

Posting an answer instead of a comment because the comment wouldn't let me put in the code I wanted to include.

Grant,

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:

DECLARE @t1 TABLE (col1 char(1) , col2 CHAR(1))
DECLARE @t2 TABLE (col1 char(1) , col2 CHAR(1))

INSERT INTO @t1 
SELECT 'A', 'Z' UNION ALL 
SELECT 'B', 'Y' UNION ALL 
SELECT 'C', 'X' UNION ALL 
SELECT 'D', 'W' 

INSERT INTO @t2
SELECT 'a', 'w' UNION ALL 
SELECT 'b', 'x' UNION ALL 
SELECT 'E', 'Y' UNION ALL 
SELECT 'F', 'Z' 

SELECT
    T2.col1,
    T2.col2
FROM
    @t2 AS T2 LEFT JOIN
    @t1 AS T1
        ON T2.col1 = T1.col1 AND
            T1.col2 = 'W'

SELECT
    T2.col1,
    T2.col2
FROM
    @t2 AS T2 LEFT JOIN
    @t1 AS T1
        ON T2.col1 = T1.col1 
Where
            T1.col1 = 'A'

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.

6 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield avatar image Matt Whitfield ♦♦ commented ·
+1 - you're absolutely right. I need more coffee today!
0 Likes 0 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
+1 I need more coffee today too. I wasn't tracking properly.
0 Likes 0 ·
Håkan Winther avatar image Håkan Winther commented ·
+1 - that was exactly my point with my comment to Grant
0 Likes 0 ·
Håkan Winther avatar image Håkan Winther commented ·
You can use the second query if you add "OR t.Col1 IS NULL" but that is a much worse mistake from a performance perspective and from readability perspective.
0 Likes 0 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
Whoa, wait a second, the WHERE clause doesn't convert queries to inner joins, that's a little backward. Instead, the query is filtering based on values from the right table where the matching criteria is incorrect. A better example would be to use 'Z' as the filtering mechanism. Then you'll notice that you're not getting values filtered at all in the join. In other words, the same data comes back if 'Z' is in the ON clause or not.
0 Likes 0 ·
Show more comments

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.