question

In this case, short answer is It will not matter. They would have the same execution plan and same throughput. The optimizer is smart enough to handle them as the same :) But I will prefer the WHERE clause for better readability.

Whilst the query is an inner join, then yes there is no difference. If the query was to be changed to an outer join, then it's a different story. Lets show an example declare @table1 table (id int, fruit varchar(50)) insert into @table1 select 1, 'apple' insert into @table1 select 2, 'banana' insert into @table1 select 3, 'pear' insert into @table1 select 4, 'peach' declare @table2 table (id int, fruitid int, colour varchar(50), is_deleted_ind char(1)) insert into @table2 select 1, 1, 'red', 'N' insert into @table2 select 1, 1, 'green','N' insert into @table2 select 1, 2, 'yellow','N' insert into @table2 select 1, 3, 'green','N' insert into @table2 select 1, 4, 'red','N' insert into @table2 select 1, 1, 'blue','Y' insert into @table2 select 1, 5, 'red', 'N' So we can see that these 2 queries return the same 5 rows select * from @table1 table1 inner join @table2 table2 on table1.id = table2.fruitid and table2.is_deleted_ind = 'N' select * from @table1 table1 inner join @table2 table2 on table1.id = table2.fruitid where table2.is_deleted_ind = 'N' but changing to a outer join (where the `is_deleted_ind` is on the side of the join that we want all the rows from) select * from @table1 table1 right join @table2 table2 on table1.id = table2.fruitid and table2.is_deleted_ind = 'N' select * from @table1 table1 right join @table2 table2 on table1.id = table2.fruitid where table2.is_deleted_ind = 'N' The first query returns 7 rows as the `is_deleted_in` is part of the JOIN condition id fruit id fruitid colour is_deleted_ind ----------- -------- ----------- ----------- ---------- -------------- 1 apple 1 1 red N 1 apple 1 1 green N 2 banana 1 2 yellow N 3 pear 1 3 green N 4 peach 1 4 red N NULL NULL 1 1 blue Y NULL NULL 1 5 red N (7 row(s) affected) but the second query returns 6 rows as the `is_deleted_ind` is a filter on the result of the join, rather than as part of the join id fruit id fruitid colour is_deleted_ind ----------- -------- ----------- ----------- ---------- -------------- 1 apple 1 1 red N 1 apple 1 1 green N 2 banana 1 2 yellow N 3 pear 1 3 green N 4 peach 1 4 red N NULL NULL 1 5 red N (6 row(s) affected) So what's the point of all this.... Code the query to be right. If `is_deleted_ind` is part of the join condition, then put it there, otherwise if it is a filter then put it in the where clause. This will prevent a whole heap of headaches if someone comes along in the future and simply changes an inner join to an outer join.