Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

which runs first:joins or where clause

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.

like image 694
Brijesh Kushwaha Avatar asked Jan 08 '13 06:01

Brijesh Kushwaha


2 Answers

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.

like image 90
Kristine S Avatar answered Sep 17 '22 18:09

Kristine S


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
like image 44
Vishwanath Dalvi Avatar answered Sep 17 '22 18:09

Vishwanath Dalvi