Condition on Join or Where?

Question

Is there any difference if I use:

select * 
from a left join b on a.id = b.id and b.id2=1
where ...

or

select * 
from a left join b on a.id = b.id 
where 
b.id2=1
Sent on:
Fri

?

The first SQL returned me super fast, already the second did not run due to MYSQL not being able to use the index of a table field.

Answer 1

Yes there is a difference, as you can see. At least it exists in current Mysql.

Of course not seeing the whole expression (has a reticence there) it may be that there was a reason to be different.

In thesis he could have optimized both the same, only he did not. Who knows do in the future. Other databases can do. It is not something inherent in SQL.

This is a case that can be optimized, but a little more complicated to understand. Optimizations are done whenever it is possible to prove mathematically that it is advantageous and that someone has created a code for it.

The JOIN is completely unnecessary in SQL. You can always get the same result without it. It exists because it gives more semantics to the intent and facilitate the optimizer to realize what is desired and optimize better.

Answer 2

Exist, and your query can still bring divergent data depending on the fields used in the filters as in the example below (done in sql server) problem I had in that question.

declare @tabela1 table(id int , decr varchar(10))

declare @tabela2 table(id int , id2 int null, decr varchar(10))

insert into @tabela1 values
(1, 'tabela 1'),(0, 'tabela 1'),(0, 'tabela 1'),(0, 'tabela 1'),(0, 'tabela 1'),
(0, 'tabela 1'),(0, 'tabela 1'),(0, 'tabela 1'),(0, 'tabela 1'),(0, 'tabela 1'),
(0, 'tabela 1')

insert into @tabela2 values
(1, 1, 'tabela 2'),(1, 2, 'tabela 2'),(0, 1, 'tabela 2'),(0, 1, 'tabela 2')

select * 
from @tabela1 a
left join @tabela2 b
on b.id = a.id and a.id = 1

select * 
from @tabela1 a
left join @tabela2 b
on a.id = b.id 
where a.id = 1

This is a case of non-standard database, where data was entered through spreadsheet files (Excel).

Something else;

When you do OUTER JOINs (ANSI-89 or ANSI-92), the criteria specified in ON clause is applied before JOIN, but when it is applied in clause WHERE is applied after the junction is done. This can produce many different sets of results and of course a difference in performance.