11
I needed to retrieve information that was in a certain table, but were not in another one. Searching found that NOT EXISTS would serve this case, but I saw that Left Join brings the same result:
NOT EXISTS
select pedido from logintegracao as A
where not exists (select * from pagamento as B where B.idPedido = A.Pedido)
Implementation Plan:
LEFT JOIN
select * from logintegracao as A
left join pagamento as B on A.Pedido = B.idpedido
where B.idpedido is null
Implementation Plan
Which Query would have the best performance? Both ways are right?
The two woods are correct.
– Diego Souza
Both are correct and the performance is the same too, if change would change very little.
– Ricardo
According to this article: Should I use NOT IN, OUTER APPLY, LEFT OUTER JOIN, EXCEPT, or NOT EXISTS?, in the presence of an
NOT EXISTSperforms better... just pay attention not to make anot exists (select * ...), up to pq you only need a column.– Tobias Mesquita
Can you run both queries by showing the "Execution plan" and posting the generated execution plans here? This is what will say the best performance, as well as the structure of your bank and indexes.
– bfavaretto
Good idea bfavaretto, I added the Execution Plan of the two Queries
– Gabriel Bernardone
There is no cake rule for such cases of always using one or the other. Ideally, run the query explain and analyze what you can improve with the Execution plan.
– gmsantos
Which key of the PAYMENT table ?
– Motta