November 18, 2015 at 7:14 am
Hi,
I have a question, in terms of performance what's best in a query with LEFT JOIN:
- Put the clause in ON of the LEFT JOIN
- Put the clause in WHERE in the end of the query
I have search this topic and some people say that's the same in theory but some people say that they have substancial increase of performance if they put the clause in ON.
What's your opinion?
November 18, 2015 at 7:24 am
scorpman (11/18/2015)
Hi,I have a question, in terms of performance what's best in a query with LEFT JOIN:
- Put the clause in ON of the LEFT JOIN
- Put the clause in WHERE in the end of the query
I have search this topic and some people say that's the same in theory but some people say that they have substancial increase of performance if they put the clause in ON.
What's your opinion?
My experience is that both options are the same in terms of performance, there could be differences in the results when moving clauses from WHERE to ON in outer joins. You can check that on this article: http://www.sqlservercentral.com/articles/T-SQL/93039/
Inner joins, will generate the same results and basically the same execution plan, so they should perform exactly the same. Don't trust me, test by yourself. I'll try to set a test harness and post back.
November 18, 2015 at 8:47 am
scorpman (11/18/2015)
Hi,I have a question, in terms of performance what's best in a query with LEFT JOIN:
- Put the clause in ON of the LEFT JOIN
- Put the clause in WHERE in the end of the query
I have search this topic and some people say that's the same in theory but some people say that they have substancial increase of performance if they put the clause in ON.
What's your opinion?
Putting the criteria in addition to the JOIN criteria in the WHERE clause in an OUTER JOIN vs in the ON clause results in a different query and changes the query to an INNER JOIN.
For example these 2 queries are going to return different results:
USE master;
GO
SELECT
T.name AS TableName,
C.name AS ColumnName
FROM
sys.tables AS T
LEFT JOIN sys.columns AS C
ON T.object_id = C.object_id
WHERE
C.name = 'dbid';
GO
SELECT
T.name AS TableName,
C.name AS ColumnName
FROM
sys.tables AS T
LEFT JOIN sys.columns AS C
ON T.object_id = C.object_id AND
C.name = 'dbid';
GO
If you look at the execution plans for the 2 queries you can see that the first query has an INNER JOIN where the second query has the expected LEFT JOIN.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 19, 2015 at 7:30 am
Except in the case of the OUTER JOIN as the other posters have outlined, there is no fundamental difference under normal circumstances. For the sake of clarity, I would advocate that you put join criteria in the JOIN clause and filtering criteria in the WHERE clause. It makes the code so much easier to understand. Of course, the exception to this is when you need to filter as part of an OUTER JOIN.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 23, 2015 at 3:18 am
Jack Corbett (11/18/2015)[hr
Putting the criteria in addition to the JOIN criteria in the WHERE clause in an OUTER JOIN vs in the ON clause results in a different query and changes the query to an INNER JOIN.
It depends on criteria. For example, this is still OUTER JOIN
SELECT
T.name AS TableName,
C.name AS ColumnName
FROM
sys.tables AS T
LEFT JOIN sys.columns AS C
ON T.object_id = C.object_id
WHERE
C.name is NULL;
NULL makes the difference.
And this one too
SELECT
T.name AS TableName,
C.name AS ColumnName
FROM
sys.tables AS T
LEFT JOIN sys.columns AS C
ON T.object_id = C.object_id
WHERE
T.name = 'dbid';
Predicate doesn't include any 'outer' table column.
November 23, 2015 at 5:30 am
scorpman (11/18/2015)
Hi,I have a question, in terms of performance what's best in a query with LEFT JOIN:
- Put the clause in ON of the LEFT JOIN
- Put the clause in WHERE in the end of the query
It has nothing to do with performance. Those two options result in logically different queries and hence different results.
The first option filters the tables before joining, the second filters after joining. Since an outer join returns all the rows in one table and matches in the other, whether the filter is done before or after changes the results.
Silly example:
CREATE TABLE Fruit (
Name VARCHAR(20),
ColourName VARCHAR(20)
);
CREATE TABLE Colours (
ColourName VARCHAR(20)
);
INSERT INTO Fruit
VALUES ('Strawberry','Red'), ('Lemon','Yellow'), ('Kiwifruit','Green');
INSERT INTO dbo.Colours
VALUES ('Red'),('Yellow'),('Green');
SELECT Name, c.ColourName FROM dbo.Fruit AS f LEFT OUTER JOIN dbo.Colours AS c ON f.ColourName = c.ColourName AND f.ColourName = 'Red';
SELECT Name, c.ColourName FROM dbo.Fruit AS f LEFT OUTER JOIN dbo.Colours AS c ON f.ColourName = c.ColourName
WHERE f.ColourName = 'Red';
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply