Mysql: Full Outer Join - How to Merge One Column

MySQL: FULL OUTER JOIN - How do I merge one column?

Use:

    SELECT t1.id,
t1.value,
t2.value2
FROM table1 t1
LEFT JOIN table2 t2 ON t2.id = t1.id
UNION
SELECT t2.id,
t1.value,
t2.value2
FROM TABLE1 t1
RIGHT JOIN TABLE2 t2 ON t2.id = t1.id

The UNION operator removes row/record duplicates, so you have to define/list the columns appropriately.

Scripts:

DROP TABLE IF EXISTS `example`.`table1`;
CREATE TABLE `example`.`table1` (
`id` int(10) unsigned NOT NULL default '0',
`value` varchar(45) NOT NULL default ''
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO table1 VALUES (1, 'a'), (2, 'c'), (3, 'e');

DROP TABLE IF EXISTS `example`.`table2`;
CREATE TABLE `example`.`table2` (
`id` int(10) unsigned NOT NULL default '0',
`value2` varchar(45) NOT NULL default ''
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO table2 VALUES (1, 'b'), (3, 'd'), (4, 'f');

Edit: Fixed line above

Outer Join merge column

According to the attached screenshot, it seems as though only one of the two columns can have a value, and the other is always null. If this is true, you could coalesce the results into a single column:

SELECT          table1.a, COALESCE(table1.b, table2.b) AS "chocolate"
FROM table1
FULL OUTER JOIN table2 ON table1.c = table2.c
WHERE table1.a > 100

How can I do a FULL OUTER JOIN in MySQL?

You don't have full joins in MySQL, but you can sure emulate them.

For a code sample transcribed from this Stack Overflow question you have:

With two tables t1, t2:

SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
UNION
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id

The query above works for special cases where a full outer join operation would not produce any duplicate rows. The query above depends on the UNION set operator to remove duplicate rows introduced by the query pattern. We can avoid introducing duplicate rows by using an anti-join pattern for the second query, and then use a UNION ALL set operator to combine the two sets. In the more general case, where a full outer join would return duplicate rows, we can do this:

SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
UNION ALL
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id
WHERE t1.id IS NULL

SQL Full Outer Join and combining two columns

You are not looking for a join :-) A join combines records. What you are after is the union of two query results.

select UserID, PermissionTableA as Permission
from TableA
UNION
select UserID, PermissionTableB
from TableB
where UserID in (select UserID from TableA)
order by UserID, Permission;

UPDATE: You may as well use your outer join with NZ. Sorry, it took me some time to see what the query was supposed to do.

select UserId, NZ(PermissionTableA, PermissionTableB) as Permission
from ...

How do I merge OUTER JOIN and UNION results?

You can use coalesce to take the non-null assetID from whatever table has it:

SELECT COALESCE(TableA.AssetID, TableB.AssetID) TableA.Valuable, TableB.Protected
FROM
(
SELECT
AssetID,
Valuable
FROM
TableA
) ta

FULL OUTER JOIN

(SELECT
AssetID,
Protected
FROM
TableB

) tb

ON ta.AssetID=tb.AssetID

Note: You probably don't need the sub-queries, though, and omitting them can simplify the query considerably:

SELECT COALESCE(TableA.AssetID, TableB.AssetID) TableA.Valuable, TableB.Protected
FROM TableA
FULL OUTER JOIN TableB
ON TableA.AssetID=TableB.AssetID

How to do full outer join to combine two tables in mysql?

In order to do the FULL OUTER JOIN you can do the LEFT OUTER JOIN and UNION with RIGHT OUTER JOIN (provided that MySql still does not support FULL OUTER JOIN):

select * from A as a
left outer join B as b on a.col = b.col
union
select * from A as a
right outer join B as b on a.col = b.col

Note that you can use subqueries for A and B - which should work with your queries. In your case:

select * from (SELECT * FROM t1) as a
left outer join (SELECT * FROM t2) as b on a._tid = b._tid
union
select * from (SELECT * FROM t1) as a
right outer join (SELECT * FROM t2) as b on a._tid = b._tid

With result being equal to (provided that I didn't my a mistake in copy-pasting your data):

+------+-------+------------+----------+------+-------+------------+----------+
| _id | _tid | _dt | _advance | _id | _tid | _dt | _advartn |
+------+-------+------------+----------+------+-------+------------+----------+
| 17 | hjg | 2012-04-18 | 2151 | NULL | NULL | NULL | NULL |
| 22 | RKT01 | 2012-04-10 | 2098 | NULL | NULL | NULL | NULL |
| 14 | RKT04 | 2012-04-18 | 1511 | 8 | RKT04 | 2012-04-20 | 150 |
| 16 | RKT09 | 2012-04-09 | 250 | NULL | NULL | NULL | NULL |
| 15 | RKT10 | 2012-04-17 | 1313 | NULL | NULL | NULL | NULL |
| 8 | RKT21 | 2012-04-03 | 1321 | NULL | NULL | NULL | NULL |
| 19 | RKT31 | 2012-04-26 | 2512 | NULL | NULL | NULL | NULL |
| 20 | RKT33 | 2012-04-10 | 2250 | NULL | NULL | NULL | NULL |
| 25 | T01 | 2012-04-11 | 2500 | NULL | NULL | NULL | NULL |
| NULL | NULL | NULL | NULL | 9 | RKT02 | 2012-04-10 | 2500 |
+------+-------+------------+----------+------+-------+------------+----------+

FULL OUTER JOIN with union subqueries

I just don't see the point for union all here. Both queries are the same, only the date filter changes. So, use conditional aggregation:

slect ggportal as portal, 
sum(case when o.ggdate >= '2020-01-01' and o.ggdate < '2021-01-01' then oa.ggamount else 0 end) as amount_2020,
sum(case when o.ggdate >= '2019-01-01' and o.ggdate < '2020-01-01' then oa.ggamount else 0 end) as amount_2020
from orders as o
left orderarticles as oa on o.ggauftragsnr = oa.ggauftragsnr
where o.ggdate >= '2019-01-01' and o.ggdate < '2021-01-01'
group by ggportal

Using FULL OUTER JOIN and/or UNION to combine tables with like fields

The question you linked to is not applicable to your question.

Here are the some forms of combining to try out:

Full Outer Join

SELECT 
COALESCE(T1.Col1,T2.Col1) As Col1,
COALESCE(T1.Col2,T2.Col2) As Col2
FROM
T1
FULL OUTER JOIN
T2
ON T1.ID = T2.ID

Matches on ID to combine rows.
ID must be unique in both tables or you'll get 'duplicate' rows

Union All

SELECT 
T.ID,
MAX(T.Col1) As Col1,
MAX(T.Col2) As Col2
FROM
(
SELECT Col1,Col2 FROM T1
UNION ALL
SELECT Col1,Col2 FROM T2
) T
GROUP BY T.ID

Effectively matches on ID

This will not introduce duplicates if ID is not unique. It will arbitrarily pick a combination of values

Here's how you just return all the combined rows and selectively combine columns. In this example, C1 only exists in table T1

SELECT C1,C2, C3 
FROM T1
UNION ALL
SELECT NULL, C2,C3
FROM T2

The important question is: do you have a unique key on each table? Do you need to match on it?



Related Topics



Leave a reply



Submit