MYSQL - Adding result set A to result set B? Please see my example

Question

I have two mysql tables.

They are laid out in this manner:

user_info

id | emailContact
_______________________
 1 | person@example.com
 3 | anotherPerson@example.com


user_biz_info

id | emailContact
_________________________
 8 | business@example.com
 9 | anotherBusiness@example.com

What kind of join would I use to create a result set of information like this:

id | emailContact
________________________________
1 - person@example.com
3 - anotherPerson@example.com
8 - business@example.com
9 - anotherBusiness@example.com

I have tried the following:

SELECT p.id, p.emailContact, b.id, b.emailContact
FROM user_info p, user_business_info b

But it seems that this is an incorrect approach.

Would someone be able to suggest the correct approach in this or possibly point me in the direction of some tutorials that cover this type of mysql join, as this is what I assume is needed in this case.

Thanks for your time in reading through my question!!

score 2 · Accepted Answer · answered Jun 11 '12 at 10:36

2

Use UNION:

SELECT id, emailContact FROM user_info
UNION ALL
SELECT id, emailContact FROM user_business_info

answered Jun 11 '12 at 10:36

eggyal

122,705
18
212
237

Hi, thanks for your response. Why have you chosen to use UNION ALL while @Jeff uses a plain UNION? – Craig van Tonder Jun 11 '12 at 10:37
@BlackberryFan: By default `UNION` excludes duplicates. `ALL` ensures that they're included. It's not clear from your question which you want, but I assumed the latter (it also performs better as MySQL doesn't have to waste resource in scanning for such duplicate results). – eggyal Jun 11 '12 at 10:39

score 2 · Answer 2 · answered Jun 11 '12 at 10:36

2

Use a UNION

e.g.

SELECT p.id, p.emailContact FROM user_info p
UNION
SELECT b.id, b.emailContact FROM user_business_info b

answered Jun 11 '12 at 10:36

Jeff Watkins

6,343
16
19

Hi, Thanks for your reply, please see the question in the answer by eggyal – Craig van Tonder Jun 11 '12 at 10:38
Union All allows duplicates, if you want duplicates then use it, if you want a regular union, don't use it. It depends on your use-case. – Jeff Watkins Jun 11 '12 at 10:41

MYSQL - Adding result set A to result set B? Please see my example

2 Answers2