mysql union of 2 tables

Question

I have 2 tables:

table A (id, user_id, flag)

table B (id, user_id, flag)

Here If I take Count of table A it comes as 10 and that of B 5

So Total = 10 + 5 = 15.

SELECT * FROM table A
LEFT JOIN table B ON table B.user_id = table A.user_id
UNION ALL
SELECT * FROM table A
RIGHT JOIN table B ON table B.user_id = table A.user_id

So It should Come 15 instead it showing 50.

Plz add the data of the tables. – Ankit Bajpai Nov 04 '14 at 08:41 — Ankit Bajpai, Nov 04 '14 at 08:41

score 2 · Answer 1 · edited May 23 '17 at 12:20

2

use

SELECT * FROM TABLE1 UNION 
SELECT * FROM TABLE2

UNION removes duplicate records in other hand UNION ALL does not.Check HERE

edited May 23 '17 at 12:20

Community

1
1

answered Nov 04 '14 at 07:52

Pratik Joshi

11,485
7
41
73

My Scenario is like i want Full outer join concept which is not possible i guesss – rahulbhondave Nov 04 '14 at 07:55
In your code , replace UNION ALL -> UNION , check what output u get – Pratik Joshi Nov 04 '14 at 08:39

score 1 · Answer 2 · answered Feb 14 '20 at 23:38

You need to make sure the data in your tables are correct.

Table A Should have 15 rows

SELECT COUNT(*) as 'rowCountTableA' FROM table_a;

Table B Should have 5 rows

SELECT COUNT(*) as 'rowCountTableB' FROM table_b;

If your tables are correct and have exactly matching column names you can join them together by specifying asterisks to get all column values.

If however the columns in your table have a few different column names that are in table_a that are not in table_b you must call out the column names instead of using asterisk to get all values.

EXAMPLE:

SELECT (id,user_id,flag) FROM table_a
UNION ALL
SELECT (id,user_id,flag) FROM table_b

mysql union of 2 tables

2 Answers2