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.
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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With