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(at)fireserve(dot)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