Обсуждение: NOT EXISTS or LEFT JOIN which one is better?

Поиск
Список
Период
Сортировка

NOT EXISTS or LEFT JOIN which one is better?

От
AI Rumman
Дата:
I can write a query to solve my requirement in any of the followings :-

1.
select *
from a
where NOT EXISTS
(
select 1
from b
where a.id = b.id)
union all
select *
from b


2.
select 
(
case when b.id is not null then
   b.id
   else
   a.id
) as id
from a
left join b
  on a.id = b.id

Any one please tell me which one is better?

Re: NOT EXISTS or LEFT JOIN which one is better?

От
Rich
Дата:
Al I have looked at this before and I am not sure the effort is worth all the thought about it.  Let your explain tell you which is better.  I read this link a year ago.
http://stackoverflow.com/questions/227037/can-i-get-better-performance-using-a-join-or-using-exists

On Sun, Apr 29, 2012 at 5:27 AM, AI Rumman <rummandba@gmail.com> wrote:
I can write a query to solve my requirement in any of the followings :-

1.
select *
from a
where NOT EXISTS
(
select 1
from b
where a.id = b.id)
union all
select *
from b


2.
select 
(
case when b.id is not null then
   b.id
   else
   a.id
) as id
from a
left join b
  on a.id = b.id

Any one please tell me which one is better?

Чтобы сделать работу с сайтом удобнее, мы используем cookie и аналитический сервис «Яндекс.Метрика». Продолжая пользоваться сайтом, вы соглашаетесь с их использованием.