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.

Answer 1

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.

Answer 2

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