Why does a join add duplicate column names-CodePudding

I have two tables and when I do a join it joins the tables together side by side not doing row based if same columns and not sure why this could be.

 -------- -------- -------- 
|   VALUE|   DAY  |  Color
 -------- -------- -------- 
|20      |MON     |    BLUE|
 -------- -------- -------- 


 -------- -------- -------- 
|   VALUE|   DAY  |  Color |
 -------- -------- -------- 
|20      |MON     |    RED |
 -------- -------- --------

I am getting the below table when join on key VALUE the above two inputs:

 -------- -------- -------- -------- -------- 
|   VALUE|   DAY  |  Color |DAY     |Color   |
 -------- -------- -------- -------- -------- 
|20      |MON     |    BLUE|Mon     |Red     |
 -------- -------- -------- -------- --------

I want to get something like below like row based

 -------- -------- -------- 
|   VALUE|   DAY  |  Color |
 -------- -------- -------- 
|20      |MON     |  BLUE  |
|20      |MON     |  RED   |
 -------- -------- --------

CodePudding user response：

You need to provide a sample SQL you are using, none the less you need to use union instead of join.

SELECT value, day, color FROM table1
UNION ALL
SELECT value, day, color FROM table2;

CodePudding user response：

The JOIN doesn't add duplicate columns or column names. It basically selects all columns from all joined tables (unless you provide those columns that should be selected). In your case, the column names in both tables are identic. If you want to prevent this, you could of course rename the columns. If you don't want to rename them in the DB, but just want to have different names in your result set, you can use aliases, meaning something like:

SELECT t1.value AS value1, t1.day AS day1, t1.color AS color1,
t2.value AS value2, t2.day AS day2, t2.color AS color2
FROM table1 JOIN table2 ON t1.value = t2.value;

If you also want to select the entries of table1 if no identic in table2 having the same value exists or vice versa, you can use LEFT JOIN or RIGHT JOIN. Have a look here how JOIN works: explanation

The output you've shown lets assume you don't even want to join your tables at all, but just do UNION, meaning select both the entries of table1 and table2. Basically, this can be done like this:

SELECT value, day, color FROM table1
UNION
SELECT value, day, color FROM table2;

You have to check if you want to select identic entries twice (in this case, you would use UNION ALL instead of UNION). And you maybe should add a meaningful WHERE clause and/or GROUP BY conditions.