0

I have the following queries:

SELECT count(*)
FROM User u
INNER JOIN NAME n 
     ON u.UserId = n.UserId
    AND n.last = 'Joe';
--returns 1943

SELECT count(*)
FROM User u
WHERE u.UserId IN (
        SELECT n.UserId
        FROM NAME n
        WHERE n.last = 'Joe'
        );
--returns 1875

UserId is the primary key in table User, and a foreign key in table Name (not unique).

How come the join query returns more rows than the nested select query? Shouldn't they be the same?

Thanks.

Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118
johng2
  • 27
  • 1
  • 5

1 Answers1

1

The join equivalent of this query:

SELECT count(*)
FROM User u
WHERE u.UserId IN (
        SELECT n.UserId
        FROM NAME n
        WHERE n.last = 'Joe'
       );

would be:

SELECT count(distinct u.UserId)
FROM User u JOIN
     NAME n
     ON n.last = 'Joe' AND u.UserId = n.UserId;

The distinct takes care of the duplicates.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786