IN or JOIN - Mailing list pgsql-general

please CC me as I am on digest
-------------------------------


I have three tables, simplified for, well, simplicity :-)

CREATE TABLE Usrs (
    usr_id serial primary NOT NULL,
    name text NOT NULL,
    login text NOT NULL,
    CONSTRAINT PK_Usrs PRIMARY KEY (usr_id)
);

CREATE TABLE EmailAddrs (
    email_addr_id SERIAL NOT NULL,
    email_addr VARCHAR(255) NOT NULL UNIQUE,
    CONSTRAINT PK_EmailAddrs PRIMARY KEY (email_addr_id)
);

CREATE TABLE UsrEmails (
    usr_id INT4 NOT NULL,
    email_addr_id INT4 NOT NULL,
    CONSTRAINT PK_UsrEmails PRIMARY KEY (usr_id, email_addr_id)
);

ALTER TABLE UsrEmails
    ADD CONSTRAINT EmailAddrs11_0MUsrEmail
    FOREIGN KEY (email_addr_id)
    REFERENCES EmailAddrs (email_addr_id);

ALTER TABLE UsrEmails
    ADD CONSTRAINT UsrEmailTypes11_0MUsrEmails
    FOREIGN KEY (usr_email_type_id)
    REFERENCES UsrEmailTypes (usr_email_type_id);


multiple 'Usrs' can have the same name, but different logins.


I want to find the count of usrs that:
    have the name 'some_name'
and
    have the email 'some_email'

-----------------------------
Should I use a JOIN or an IN?
If the 'IN' example below is right, and there is either:

    NO Usr with name='some_name'
    OR
    NO email with email='some_email'

will it return a NULL, or a '0' count?

my thought for an IN:
----------------------
SELECT COUNT(*)
FROM UsrEmails
WHERE
    usr_id IN
        (SELECT usr_id
         FROM Usrs
         WHERE name='some_name'::text)
    AND
    email_addr_id=(SELECT email_addr_id
                   FROM Emails
                   WHERE email='some_email'::text);