Thread: join vs exists

join vs exists

From
AI Rumman
Date:
Which one is good - join between table or using exists in where condition?

Query 1;

Select a.*
from a
where exists
(
select 1 from b inner join c on b.id1 = c.id where a.id = b.id)

Query 2:
select a.*
from a
inner join
(select b.id from b inner join c on b.id1 = c.id) as q
on a.id = q.id

Any suggestion please.

Re: join vs exists

From
"Kevin Grittner"
Date:
AI Rumman  wrote:

> Which one is good - join between table or using exists in where
> condition?

Your example wouldn't return the same results unless there was at
most one matching row in b and one matching row in c, at least
without resorting to DISTINCT (which you don't show).  So, be careful
of not getting the wrong results in an attempt to optimize.

You don't say which version of PostgreSQL you're using, but if its a
fairly recent major version, I would expect nearly identical
performance if the queries returned the same results without
DISTINCT, and would usually expect better results for the EXISTS than
the JOIN with DISTINCT.

-Kevin