Is there a 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, the second one did not run because MYSQL could not use the index of a table field.
Exist, and your query might still be able to 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 non-standard database case, where data was inserted through spreadsheet inserts (Excel).
Another thing;
When you do OUTER JOINs (ANSI-89 or ANSI-92) , the criteria specified in the ON clause is applied before the JOIN, but when applied in the WHERE clause is applied after the join is made. This can produce many different sets of results and of course a difference in performance.
Yes there is a difference, as you may notice. At least it exists in the current MySQL.
Of course not seeing the whole expression (there is a reticence there) there might be a reason to be different.
In theory he could have optimized both equally, except he did not. Maybe you do in the future. Other databases can do this. It is not something inherent in SQL.
This is a possible case of optimizing, but a little more complicated to realize. Optimizations are made whenever it is possible to prove mathematically that it is advantageous and that someone has created a code for it.
JOIN is completely unnecessary in SQL. You always get the same result without it. It exists because it gives more semantics to the intent and makes it easier for the optimizer to perceive what is desired and optimize better.