Search code examples
sqlselectjoinsybasesybase-ase15

Join query returns more rows than nested select query?

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.

Solution

  • 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.