Joining two similar tables in MySQL

Question

I have two tables with similar columns - let's say table A with column LABEL_A and table B with column LABEL_B. The data types of LABEL_A and LABEL_B are same.

How can I select LABELs from both tables in a single query? (So the the result of the query contains single column LABEL containing data from LABEL columns of both tables).

EDIT: Can I use such UNION queries in a CURSOR?

Thanks for answers.

score 5 · Accepted Answer · answered Apr 14 '10 at 19:37

5

Use:

SELECT a.label_a
  FROM TABLE_A a
UNION ALL
SELECT b.label_b
  FROM TABLE_B b

UNION ALL will be faster, but won't remove duplicates if they exist. Use UNION if you want duplicates removed.

answered Apr 14 '10 at 19:37

OMG Ponies

325,700
82
523
502

Works, thanks. And what if I want to assign the resulting UNIONed column a name? – NumberFour Apr 14 '10 at 19:45
1

@NumberFour: Alias the column appropriately in the statement before the UNION [All]. IE: `SELECT a.label_a AS labelx` - don't need to define an alias for columns after the UNION. – OMG Ponies Apr 14 '10 at 19:50
And can I use the UNION in the CURSOR? – NumberFour Apr 14 '10 at 19:51
1

@NumberFour: I don't see anything in the documentation that suggests you can't use a `UNION` in a cursor: http://dev.mysql.com/doc/refman/5.0/en/union.html – OMG Ponies Apr 14 '10 at 20:09

score 3 · Answer 2 · answered Apr 14 '10 at 19:36

3

Use a UNION:

select LABEL_A as Label from A
union
select LABEL_B as Label from B

answered Apr 14 '10 at 19:36

Powerlord

87,612
17
125
175

Joining two similar tables in MySQL

2 Answers2

Linked