MySql select all from two tables result in a lot of duplicates

Question

I'm using mysql db from oracle (latest version 8.*). I'm using node js with express. I have several tables with the exact same layout. All using an auto_increment id and some columns. For the index page. I need the fetch all data from multiple tables. I have the following tables: beers (100 rows), non_alcoholic_beers(7 rows) and red_wines(50 rows).

Accordingly to the Oracle documentation a simple: SELECT * FROM table1, table2; or SELECT * FROM table1 join table2; should suffice. It's works kinda....

But if I do this I get 52+K records. A lot of duplicates thus. I only expect 157 records.

A simple: 'SELECT * FROM table1;' works perfectly.

Can anyone show some light on this matter? Thanks in advance

Also, Please stop using comma based Implicit joins and use [Explicit `Join` based syntax](https://stackoverflow.com/questions/44917/explicit-vs-implicit-sql-joins) — Madhur Bhaiya, Oct 20 '18 at 08:45

score 1 · Accepted Answer · edited Oct 20 '18 at 09:06

1

If your tables are not related to each other (no foreign key) and you just want to get all the results combined you should use UNION. It merges the result of queries . You can try it like

Select * from table1 
UNION 
Select * from table2

edited Oct 20 '18 at 09:06

Madhur Bhaiya

28,155
10
49
57

answered Oct 20 '18 at 08:45

Abdullah Razzaki

972
8
16

Union requires both select queries to have same no of columns. This query will fail if both the tables dont have same no of columns – Madhur Bhaiya Oct 20 '18 at 08:51
He said he has multiple tables with exact same layout – Abdullah Razzaki Oct 20 '18 at 08:54

MySql select all from two tables result in a lot of duplicates

1 Answers1