0

Option 1:

SELECT a.id
FROM   table_a AS a
       INNER JOIN table_b AS b
               ON a.id = b.id
                  AND a.name = 'David';

Option 2:

SELECT a.id
FROM   table_a AS a
       INNER JOIN table_b AS b
               ON a.id = b.id
WHERE  a.name = 'David';

Is there any difference in the result of these 2 SQL statements? Is there any performance impact if we write filters in Join clause?

When I tried these 2 queries, I saw the same results and performance was not impacted. Please clarify

Thom A
  • 88,727
  • 11
  • 45
  • 75
Raghavendra Maiya
  • 1
  • 1
  • 1
    For inner join it makes no difference. it is probably easier to read if the join condition has the predicate involving both tables and the where condition any predicates just referencing one though (i.e. your "Option 2") – Martin Smith Aug 01 '23 at 12:11
  • See also [difference ON AND WHERE clause](https://stackoverflow.com/questions/4694281/is-it-better-to-do-an-equi-join-in-the-from-clause-or-where-clause/) – Jonas Metzler Aug 01 '23 at 12:15
  • Looks like the SQL Server specific edge case I mention there does still exist and the deprecation still hasn't been followed through on! https://dbfiddle.uk/DyERVNkp – Martin Smith Aug 01 '23 at 12:21

0 Answers0