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