I have two queries.
1)
select t1.PK
from table1 as t1
JOIN table2 as t2 on t1.pk=t2.pk ....
WHERE t1.someattribute=somecondition
select t1.PK
from table1 as t1
JOIN table2 as t2 on (t1.pk=t2.pk and t1.someattribute=somecondition)
Which one would perform better. My understanding was that, the second one was better because while it is joining the two tables it is selecting which records it wants to work with and therefore it won’t keep in memory a huge number of rows (depending on the size of the table) that when the WHERE clause is processed will be discarded and only were kept in memory for nothing.
But when I test them I don’t see a big difference. Could anybody confirm/deny if the premise that adding an AND while the join is happening reduces the number of rows that the table works with is true or not?
>Solution :
SQL is a declarative language. You describe what you want via SQL, and the system to which you submit that SQL decides how to get it for you. In other words, you don’t get much say about HOW it gets to that result. As such, in just about every RDBMS out there, these two statements are synonymous.
After parsing the sql, the system will perform optimization steps to execute as quickly as possible. Any RDBMS worth its salt will perform an optimization step called "predicate pushdown" in which it will take conditions in the WHERE clause and determine if it can apply them to data selection when it reads the tables.
It would be kind of silly to select EVERYTHING and only after shuffling the data together through all the joins finally look at the WHERE clause. At the end of the day Microsoft isn’t in the cloud business to burn compute unnecessarily, or maybe it is. I don’t work there.
At any rate, I don’t have an Azure instance to test, but if you want to confirm you can run an explain/execution plan for both queries and see for yourself if there any differences. Likely they will be the same.