Why is there such a huge performance difference in these two join statements? I have a query which i first wrote as (query has been simplified a bit) :

select ROW_NUMBER() OVER (ORDER BY c.CategoryTypeID), c.name, count(p.id) from category c
inner join product p on p.indID = c.id or p.catid = c.id and p.deleted = 1 and p.statusid = 1 
group by c.name


This took 8 seconds.

If i changed it to the following it runs in under 1 second:

   select ROW_NUMBER() OVER (ORDER BY c.CategoryTypeID), c.name, count(p.id) from category c
   inner join product p on p.indID = c.id or p.catid = c.id
   where p.deleted = 0 and p.statusid =1 
group by c.name


I'd like to understand why there's such a huge difference in performance. Not only is the performance worse but it seems to be ignoring my conditional statements on the first sql query it's counting records regardless of what p.deleted or p.statusid is

What I am not understanding about how joins work? This is being run on mssql 2012
The OR operator in your first statement is making the output of both queries totally different.  If you put brackets around the two items that are separated by the OR statement you will see the results are the same.

SELECT ROW_NUMBER() OVER (ORDER BY c.CategoryTypeID), c.name, count(p.id) 
FROM category c 
    INNER JOIN product p on (p.indID = c.id or p.catid = c.id) 
        and p.deleted = 1 
        and p.statusid = 1 
GROUP BY c.name


OR is evaluated after AND...