mysql union of 2 tables2014-11-04 mysql union
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.
SELECT * FROM TABLE1 UNION SELECT * FROM TABLE2
UNION removes duplicate records in other hand UNION ALL does not.Check HERE
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.
SELECT (id,user_id,flag) FROM table_a UNION ALL SELECT (id,user_id,flag) FROM table_b
- How do I import an SQL file using the command line in MySQL?
- Should I use the datetime or timestamp data type in MySQL?
- What is the difference between UNION and UNION ALL?
- Insert into a MySQL table or update if exists
- Using union and order by clause in mysql
- How to get the sizes of the tables of a MySQL database?
- Can I concatenate multiple MySQL rows into one field?
- SQL query return data from multiple tables
- UPDATE multiple tables in MySQL using LEFT JOIN