1. I have a inner join query with 2 tables with a where clause on the 1st table. 2. I have a left outer join query with 2 tables with a where condition on the left table. 3. I have a left outer join query with 2 tables with a where condition on the left table which is "where left_table.col is not null". My understanding is in all the cases, the data will be pulled up with where condition first and then the join will be applied on. Am I wrong somewhere, since for the query plan for the 3d query shows all the record before joining. Please help me in correcting my knowledge.
If you look at the execution plan, you can see the order in which the processing will occur. SQL Server will rearrange things as it needs to in order to satisfy the data needed to return and make it perform well. You could get filtering early (applying WHERE clause first), or you could see the filtering done later (applying the WHERE clause late). It just depends on the query and the interpretation of that query within the query optimizer. The execution plan is how you look at that.