Re: IN or JOIN

I can't imagine a scenario where the IN would be a better choice. Is
there a reason you think the JOIN is not appropriate?

-tfo

On Sep 25, 2004, at 7:20 PM, gearond@fireserve.net wrote:

> 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);
>
>
>
>
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faqs/FAQ.html