IN or JOIN

[gearond]

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_0M UsrEmail
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_ema il'

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_ema il'::text);



---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Thomas F.O'Connell]

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, ge*****@fireser ve.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_0M UsrEmail
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_ema il'

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_ema il'::text);



---------------------------(end of
broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Dennis Gearon]

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, ge*****@fireser ve.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_0M UsrEmail
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_ema il'

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_ema il'::text);



---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend