Re: IN or JOIN

No, I am just moving beyond table, foreign key, function, trigger, and contraint designing to more complicated queries. I just didn't know enough to choose if either would WORK at all, much less be better.

Thomas F. O'Connell wrote:
> 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
>
>
>