Subqueries vs Multi Table Join

Question

I've 3 tables A, B, C. I want to list the intersection count.

Way 1:-

select count(id) from A a join B b on a.id = b.id join C c on  B.id = C.id;

Result Count - X

Way 2:-

SELECT count(id) FROM A WHERE id IN (SELECT id FROM B WHERE id IN (SELECT id FROM C));

Result Count - Y

The result count in each of the query is different. What exactly is wrong?

The JOIN version may return duplicated id's, to be counted several times. — jarlh
– jarlh, Commented Jan 6, 2020 at 12:34

Gordon Linoff · Accepted Answer · 2020-01-06 12:35:52Z

2

A JOIN can multiply the number of rows as well as filtering out rows.

In this case, the second count should be the correct one because nothing is double counted -- assuming id is unique in a. If not, it needs count(distinct a.id).

The equivalent using JOIN would use COUNT(DISTINCT):

select count(distinct a.id)
from A a join
     B b
     on a.id = b.id join
     C c
     on B.id = C.id;

I mention this for completeness but do not recommend this approach. Multiplying the number of rows just to remove them using distinct is inefficient.

In many databases, the most efficient method might be:

select count(*)
from a
where exists (select 1 from b where b.id = a.id) and
      exists (select 1 from c where c.id = a.id);

Note: This assumes there are indexes on the id columns and that id is unique in a.

edited Jan 6, 2020 at 12:35

answered Jan 6, 2020 at 12:34

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Praful Bagai Over a year ago

Tried all 3 solutions on 10M records:- JOINs including distinct take 45secs, exists take 46 secs, and nested Select takes 52 secs.

Mansi Raval Over a year ago

Measuring time for query wont help you always to find optimum solution. Please check for query plan for each of the approach, Adding joins and than distinct will add overhead for final result.

Gordon Linoff Over a year ago

@PythonEnthusiast . . . Does the exists have the recommended indexes? Does it require select distinct?

Collectives™ on Stack Overflow

Subqueries vs Multi Table Join

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related