How it matters adding filters in table joins?

Question

Post reply

How it matters adding filters in table joins?

Suresh Arumugam

Hall of Fame

Points: 3370
More actions
May 27, 2010 at 7:36 am

#235561

Hi,
Consider the below script.
declare @TblEmp table
(
EmpID int Identity,
EmpName varchar(50),
CountryId SmallINt)
Declare @TblCountry table
(
CountryID int Identity,
CountryName varchar(100)
)
insert into @Tblcountry values ('Japan')
insert into @Tblcountry values ('India')
insert into @Tblcountry values ('China')
insert into @Tblemp values ('Emp1',1)
insert into @Tblemp values ('Emp2',2)
insert into @Tblemp values ('Emp3',3)
insert into @Tblemp values ('Emp4',1)
Which of the below query is logically correct? Would there be any performance difference between them if the tables used in links are permanent tables with millions of records?
[font="Arial Black"]select *
from @Tblemp emp
inner join @TblCountry Country on emp.CountryID = Country.CountryID
where CountryName = 'Japan'[/font]
[font="Arial Black"]select *
from @Tblemp emp
inner join @TblCountry Country on emp.CountryID = Country.CountryID and CountryName = 'Japan'[/font]
Regards,
Suresh Arumugam

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

Suresh excellent job on posting a complete example, thanks!

in this case, you want to compare the actual execution plans to see if it makes a difference.

in your example, they produce identical execution plans, but if there were a lot of rows in the data, I'd think they might vary...

I've got a bigger table of city/county/state stuff, let me see if something similar have different execution plans.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 2

For an INNER JOIN they are most likely to always produce identical execution plans and the performance will be the same. As a general practice, because of this, I advocate keeping the filter criteria in the WHERE clause and the JOIN criteria in the ON clause and keep them seperate. I do that primarily for clarity, not performance.

However, when you get very complicated plans and the optimizer times out before it can find the optimal plan, I have seen variations in execution plans between the two. If you think it might help to move it to the ON or WHERE clause from the other, I'd try it. I say this for INNER JOIN because it won't affect the data returned. LEFT/RIGHT JOIN can be different.

"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

Robert Frasca SSCertifiable Points: 6026 More actions · Answer 3

Suresh Kumar-284278 (5/27/2010)
Hi,
Consider the below script.
declare @TblEmp table
(
EmpID int Identity,
EmpName varchar(50),
CountryId SmallINt)
Declare @TblCountry table
(
CountryID int Identity,
CountryName varchar(100)
)
insert into @Tblcountry values ('Japan')
insert into @Tblcountry values ('India')
insert into @Tblcountry values ('China')
insert into @Tblemp values ('Emp1',1)
insert into @Tblemp values ('Emp2',2)
insert into @Tblemp values ('Emp3',3)
insert into @Tblemp values ('Emp4',1)
Which of the below query is logically correct? Would there be any performance difference between them if the tables used in links are permanent tables with millions of records?
[font="Arial Black"]select *
from @Tblemp emp
inner join @TblCountry Country on emp.CountryID = Country.CountryID
where CountryName = 'Japan'[/font]
[font="Arial Black"]select *
from @Tblemp emp
inner join @TblCountry Country on emp.CountryID = Country.CountryID and CountryName = 'Japan'[/font]

You might also consider making CountryID (the foreign key) the same data type as the primary key of TBLCountry (or make the primary key a smallInt). It probably wouldn't matter from a performance point of view since I presume you'll never have more than 32,767 countries but I prefer consistency between primary and foreign key definitions.

"Beliefs" get in the way of learning.

Suresh Arumugam Hall of Fame Points: 3370 More actions · Answer 4

Thanks all guys for your wonderful thoughts.

Just to understand the difference of using filters between "Joins" and "Where" clauses, I took "Country","Employee" tables and nothing in real picture.

Suresh

Regards,
Suresh Arumugam