CODEWITHSUNDEEP
mysqlunion
rahulbhondave
rahulbhondave

Reputation: 186

mysql union of 2 tables

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.

Upvotes: 2

Views: 69

Answers (2)

Alex
Alex

Reputation: 216

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

Upvotes: 1

Pratik Joshi
Pratik Joshi

Reputation: 11693

use 

SELECT * FROM TABLE1 UNION 
SELECT * FROM TABLE2

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

Upvotes: 2

Related Questions