Обсуждение: IN or JOIN
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);
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
No, I am just moving beyond table, foreign key, function, trigger, and contraint designing to more complicated queries. Ijust 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@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 > > >