which runs first:joins or where clause

Question

I have a query in which i am Using 13 LEFT OUTER JOINS .The very left table has a large data,so it takes very much time to execute the query and return the result. But when I am using a where clause to filter the result , It takes very less time. Now i am confused about which executes first :the joins which produces result and then where clause filters from it OR first it filters the result the takes the join of the result.

Keep in mind that regardless of how SQL Server's query optimizer chooses to shuffle your steps around, the best thing you can do is properly maintain and index your data so the server can more easily do its job. — Adam Maras, Jan 08 '13 at 06:45
dont forget to upvote and mark it as accepted if you got the info you wnat — Pranay Rana, Jan 09 '13 at 08:06

score 14 · Accepted Answer · edited Jan 15 '13 at 21:58

14

Generally, any DBMS (such as SQL) will do its own query optimization, which uses the algorithm it thinks is the fastest. So it's filtering, then joining.

edited Jan 15 '13 at 21:58

Taryn

242,637
56
362
405

answered Jan 08 '13 at 06:42

Kristine S

323
2
8

Pranay Rana · Answer 2 · 2013-01-08T06:49:35.780

Read : which one runs first in query execution WHERE CLAUSE orJOIN CLAUSE ?

and

I am reding post of Pinal Dave and I found this , it migh talso help you to understand

SQL SERVER – Interesting Observation of ON Clause on LEFT JOIN – How ON Clause affects Resultset in LEFT JOIN

enter image description here

understand ON clause it is apply before JOIN that is why it retrieves all the result of Table2 where there are Flag = 1 but it does not affect Table1 so it retrieves all the rows of table1. When WHERE clause is applied it applies to complete result so it removes all the rows from Table1 and Table2 where Flag is not equal to 1, essentially keeping flag = 1 rows from Table1 and Table2.

score 2 · Answer 3 · edited May 23 '17 at 12:26

Copied from my previous answer

create table A(id int);
create table B(id int);

INSERT INTO A VALUES(1);
INSERT INTO A VALUES(2);
INSERT INTO A VALUES(3);

INSERT INTO B VALUES(1);
INSERT INTO B VALUES(2);
INSERT INTO B VALUES(3);

SELECT * FROM A;
SELECT * FROM B;

id
-----------
1
2
3

id
-----------
1
2
3

Filter on the JOIN to prevent rows from being added during the JOIN process.

select a.*,b.*
from   A a left join B b 
on     a.id =b.id and a.id=2;

id          id
----------- -----------
1           NULL
2           2
3           NULL

WHERE will filter after the JOIN has occurred.

select a.*,b.* 
from   A a left join B b 
on     a.id =b.id 
where  a.id=2;

id          id
----------- -----------
2           2

which runs first:joins or where clause

3 Answers3