473,322 Members | 1,314 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

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);



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

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

Nov 23 '05 #1
2 1130
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*****@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

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

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

Nov 23 '05 #2
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*****@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


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

Nov 23 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: B. Fongo | last post by:
I learned MySQL last year without putting it into action; that is why I face trouble in formulating my queries. Were it a test, then you would have passed it, because your queries did help me...
2
by: Bruce Duncan | last post by:
I'm a bit new to MySQL (know MS SQL well...and that may be the problem...getting the syntax confused) and I'm having a join problem...can anyone offer some help? Here's my problem: I have table1...
3
by: Ike | last post by:
Oh I have a nasty query which runs incredibly slowly. I am running MySQL 4.0.20-standard. Thus, in trying to expedite the query, I am trying to set indexes in my tables. My query requires four...
1
by: Beachvolleyballer | last post by:
hi there anyone had an idea to join following 2 queries to 1???? ----- QUERY 1 --------------------------------------------- SELECT TMS_CaseF_2.Name AS TCDomain_0, TMS_CaseF_3.Name AS...
8
by: Matt | last post by:
Hello I have to tables ar and arb, ar holds articles and a swedish description, arb holds descriptions in other languages. I want to retreive all articles that match a criteria from ar and...
7
by: Greg | last post by:
I'm a quantitative securities analyst working with Compustat data (company fiscal reports and pricing feeds). My coworker came across a problem that we fixed, but I'd like to understand 'why' it...
3
by: Ian Boyd | last post by:
i know nothing about DB2, but i'm sure this must be possible. i'm trying to get a client to create a view (which it turns out is called a "Logical" in DB2). The query needs a LEFT OUTER JOIN, but...
12
by: Phil Powell | last post by:
<cfquery name="getAll" datasource="#request.dsn#"> SELECT U.userID, U.fname, U.lname, U.phone, U.lastLoggedIn, U.choiceId, U.experience, T.label AS teamLabel, R.label AS roleLabel FROM User U...
52
by: MP | last post by:
Hi trying to begin to learn database using vb6, ado/adox, mdb format, sql (not using access...just mdb format via ado) i need to group the values of multiple fields - get their possible...
12
by: Chamnap | last post by:
Hello, everyone I have one question about the standard join and inner join, which one is faster and more reliable? Can you recommend me to use? Please, explain me... Thanks Chamnap
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.