Duplicate in full join trail using mysql with duplicate column name

user_13037437 0
Viewed 72

my code is like this

select * from a left join b on a.t = b.t
UNION
select * from a right join b on a.t = b.t;

my result is like this restult

I want the result like this result2

Would anyone help?

my table structures are:

table a:
t x
- -
A 1
B 2
C 3

Table B:
t y
- -
B 2
C 3
D 4

table_code:

CREATE TABLE `a` (
  `t` varchar(1) NOT NULL,
  `x` int(1) NOT NULL,
  PRIMARY KEY (`t`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `a` VALUES ('A',1);
INSERT INTO `a` VALUES ('B',2);
INSERT INTO `a` VALUES ('C',3);

CREATE TABLE `b` (
  `t` varchar(1) NOT NULL,
  `y` int(1) NOT NULL,
  PRIMARY KEY (`t`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `b` VALUES ('B',2);
INSERT INTO `b` VALUES ('C',3);
INSERT INTO `b` VALUES ('D',4);
duplicates mysql
2 Answers

Here is how you may simulate a full outer join in MySQL:

SELECT a.t, a.x, b.y FROM a LEFT JOIN b ON a.t = b.t
UNION ALL
SELECT b.t, a.x, b.y FROM a RIGHT JOIN b ON a.t = b.t WHERE a.t IS NULL;

The main problems in your current query are that you are doing SELECT *, which means that each table will add 2 columns to the select clause (you only want 3). Also, the lower half of the union should do a null check on the a table, as it only is there to add records missed by the upper left join.

user_18632290

Try This Query For Your Expected Result. 

Select * FROM 
     (SELECT a.t, a.x, b.y FROM a LEFT JOIN b ON a.t = b.t
      UNION ALL
     SELECT b.t, a.x, b.y FROM a RIGHT JOIN b ON a.t = b.t WHERE a.t IS NULL) A 
ORDER BY A.t ASC;

Click Here To Check DBFIDDLE

Hopefully you will Undestand It. Feel free to share if you have any query.

user_57465920
Related