How it matters adding filters in table joins?
-
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 -
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! -
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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
-
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.
-
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply