Which Query is faster if we put the "Where" inside the Join Table or put it at the end?

Question

Ok, I am using Mysql DB. I have 2 simple tables.

Table1
ID-Text
12-txt1
13-txt2
42-txt3
.....

Table2
ID-Type-Text
13- 1 - MuTxt1
42- 1 - MuTxt2
12- 2 - Xnnn

Now I want to join these 2 tables to get all data for Type=1 in table 2

SQL1:

Select * from 
Table1 t1 
Join
(select * from Table2 where Type=1) t2
on t1.ID=t2.ID

SQL2:

Select * from 
Table1 t1 
Join
Table2 t2
on t1.ID=t2.ID
where t2.Type=1

These 2 queries give the same result, but which one is faster?

I don't know how Mysql does the Join (or How the Join works in Mysql) & that why I wonder this!!

Exxtra info, Now if i don't want type=1 but want t2.text='MuTxt1', so Sql2 will become

Select * from 
Table1 t1 
Join
Table2 t2
on t1.ID=t2.ID
where t2.text='MuTxt1'

I feel like this query is slower??

Doing the second one (with the Join) is certainly more standard and I think the more efficient way — Brendan, Jan 10 '14 at 05:45
i also think like u, but if doing like SQL2 then does Mysql check all the rows of table2 when doing join & finally get the type=1? & when doing like SQL1, will mysql select all type=1 first then the table2 will be smaller then it does the join?? — Tum, Jan 10 '14 at 05:48
Smart optimizers turn them into the same query (note, I'm not sure how smart MySQL's is). Unless the explain plan shows radical differences in execution speed/resources, just use whichever is your local standard, and is considered easier to maintain. Note that correlated subqueries are sometimes needed _regardless_, when doing things similar to comparison of aggregates. — Clockwork-Muse, Jan 10 '14 at 06:17
It is explained properly here [link](https://stackoverflow.com/a/4160655/1129978) — Himalaya Garg, May 21 '19 at 06:01

Upendra Chaudhari · Answer 1 · 2014-01-10T06:18:12.563

1

As per my view 2nd query is more better than first query in terms of code readability and performance. You can include filter condition in Join clause also like

Select * from 
Table1 t1 
Join
Table2 t2 on t1.ID=t2.ID and t2.Type=1

You can compare execution time for all queries in SQL fiddle here :

Query 1

Query 2

My Query

edited Jan 10 '14 at 06:18

answered Jan 10 '14 at 05:46

Upendra Chaudhari

6,473
5
25
42

but will mysql smart enough to check only type=1 when it join tb2 or it checks everything when join and finally filter out the type=1? – Tum Jan 10 '14 at 05:50
Check this : http://stackoverflow.com/questions/4160637/mysql-join-clause-vs-where-clause – Upendra Chaudhari Jan 10 '14 at 05:55

score 1 · Accepted Answer · answered Jan 10 '14 at 06:03

Sometimes the MySQL query optimizer does a pretty decent job and sometimes it sucks. Having said that, there are exception to my answer where the optimizer optimizes something else better.

Sub-Queries are generally expensive as MySQL will need to execute and store results seperately. Normally if you could use a sub-query or a join, the join is faster. Especially when using sub-query as part of your where clause and don't put a limit to it.

Select *
from Table1 t1
  Join Table2 t2 on t1.ID=t2.ID
where t2.Type=1

and

Select *
from Table1 t1
  Join Table2 t2
where t1.ID =t2.ID AND t2.Type=1

should perform equally well, while

Select *
  from Table1 t1 
Join (select *
  from Table2
  where Type=1) t2
  on t1.ID=t2.ID

most likely is a lot slower as MySQL stores the result of select * from Table2 where Type=1 into a temporary table.

Generally joins work by building a table comprised of all combinations of rows from both table and afterwards removing lines which do not match the conditions. MySQL of course will try to use indexes containing the columns compared in the on clause and specified in the where clause. If you are interested in which indexes are used, write EXPLAIN in front of your query and execute.

ok agree, but if we do the Left Join or Right Join then it will be slow since it has to take all data, but if we use Just use Join then put the Where at the End right — Tum, Jan 10 '14 at 06:08
Left or Right join tend to yield more data. Still they are normally fastest written in the same way. You should always consider joining on primary key or index. — TheConstructor, Jan 10 '14 at 06:11
Note that you can't just blindly use `RIGHT`/`LEFT` joins here - if you have another condition in the `WHERE` clause it's going to turn it into a regular `INNER JOIN` (and you **will not** get the same results). Stick as many conditions as possible into the relevant `ON` clause, and leave the `WHERE` clause for "primary table"-only (the one mentioned in the `FROM` clause) conditions. This also helps your code be more maintainable, by putting the relevant conditions close to the reference. — Clockwork-Muse, Jan 10 '14 at 06:23

score 0 · Answer 3 · answered Jan 10 '14 at 05:50

I think this question is hard to answer since we don't exactly know the internals of the query parser in the database. Usually these kind of constructions are evaluated by the database in a similar way (it can see that the first and second query are identical so parses it correctly, or not).

I would write the second one since it is more clear what is happening.

Which Query is faster if we put the "Where" inside the Join Table or put it at the end?

3 Answers3