Possbil Possbil - 1 month ago 7
MySQL Question

MYSQL FULL OUTER JOIN - All NULL results when using LEFT-UNION-LEFT JOIN

Tbl_A

cap_id| yr_a| sp_a| iso_a| area_a| qty_a | prod_a |
3| 2015| TRR| 54| 8| 120 | 0 |
678| 2015| BOM| 62| 27| 0.0 | 0 |
20| 2015| TRR| 54| 27| 0.0 | 0 |
45| 2015| FRC| 7| 15| 86800 | 0 |
52| 2015| AZB| 12| 6| 987 | 0 |


Tbl_B

aqua_id| yr_b| sp_b| iso_b| area_b| qty_b | prod_b |
78| 2015| OTS| 32| 27| 6868 | 1 |
333| 2015| FRC| 7| 15| 550 | 1 |
334| 2015| FRC| 7| 15| 550 | 2 |
789| 2015| TRR| 54| 27| 45000 | 3 |
987| 2015| TRR| 32| 27| 40 | 2 |


I got the FULL OUTER JOIN I was looking for

BUT the query is also generating a whole bunch of all NULL records (id's 7-9 in Tbl_C)

Tbl_C - Final Tbl

id| cap_id| aqua_id| yr_a| yr_b| sp_a| sp_b| iso_a| iso_b|area_a|area_b| qty_a| qty_b | prod_a | prod_b
1 | 20| 789| 2015| 2015| TRR| TRR| 54| 54| 27| 27| 0.0| 45000 | 0 | 1
2 | 45| 333| 2015| 2015| FRC| FRC| 7| 7| 15| 15| 86800| 550 | 0 | 1
3 | 45| 334| 2015| 2015| FRC| FRC| 7| 7| 15| 15| 86800| 550 | 0 | 2
4 | 678| NULL| 2015| NULL| BOM| NULL| 62| NULL| 27| NULL| 0.0| NULL | 0 | NULL
5 | 3| NULL| 2015| NULL| TRR| NULL| 54| NULL| 8| NULL| 120| NULL | 0 | NULL
6 | NULL| 78| NULL| 2015| NULL| OTS| NULL| 32| NULL| 27| NULL| 6868 | 0 | 1
7 | NULL| 987| NULL| 2015| NULL| TRR| NULL| 32| NULL| 27| NULL| 40 | 0 | 2
8 | NULL| NULL| NULL| NULL| NULL| NULL| NULL| NULL| NULL| NULL| NULL| NULL | NULL | NULL
9 | NULL| NULL| NULL| NULL| NULL| NULL| NULL| NULL| NULL| NULL| NULL| NULL | NULL | NULL


I'm trying to work out what is causing the extra multiple all NULL records?

The query used was:

(SELECT a.cap_id, b.aqua_id, a.yr_a, b.yr_b,...., a.qty_a, b.qty_b
FROM Tbl A AS a LEFT JOIN Tbl_B AS b
ON a.yr_a = b.yr_b
AND a.iso_a = b.iso_b
AND a.area_a = b.area_b
AND a.sp_a = b.sp_b
WHERE a.yr_a = 2015)
UNION
(SELECT a.cap_id, b.aqua_id, a.yr_a, b.yr_b,...., a.qty_a, b.qty_a
FROM Tbl_B AS b LEFT JOIN Tble_A AS a
ON a.yr_a = b.yr_b
AND a.iso_a = b.iso_b
AND a.area_a = b.area_b
AND a.sp_a = b.sp_b
WHERE b.yr_b = 2015);

Answer Source

DEMO: http://rextester.com/AWDYA21027 using your sample data & query I don't get the repeated null columns. This implies a data issue.

Why make it a left join? Just switch left to right and the where clause to b.yr_a.

and to eliminate the nulls ensure at least one of the join criteria match by eliminating records who have all 4 values null?

Given your sample data a full outer join would return: when ordered by cap_Id and aqua_ID

+--------+---------+------+------+-------+-------+
| CAP_ID | AQUA_ID | YR_A | YR_B | QTY_A | QTY_B |
+--------+---------+------+------+-------+-------+
|        |      78 |      | 2015 |       |  6868 |
|        |     987 |      | 2015 |       |    40 |
|      3 |         | 2015 |      |   120 |       |
|     20 |     789 | 2015 | 2015 |     0 | 45000 |
|     45 |     333 | 2015 | 2015 | 86800 |   550 |
|    678 |         | 2015 |      |     0 |       |
+--------+---------+------+------+-------+-------+

The below query returns:

+----+--------+---------+------+------+----------+----------+
|    | cap_id | aqua_id | yr_a | yr_b |  qty_a   |  qty_b   |
+----+--------+---------+------+------+----------+----------+
|  1 | NULL   | 78      | NULL | 2015 | NULL     | 6868,00  |
|  2 | NULL   | 987     | NULL | 2015 | NULL     | 40,00    |
|  3 | 3      | NULL    | 2015 | NULL | 120,00   | NULL     |
|  4 | 20     | 789     | 2015 | 2015 | 0,00     | 45000,00 |
|  5 | 45     | 333     | 2015 | 2015 | 86800,00 | 550,00   |
|  6 | 678    | NULL    | 2015 | NULL | 0,00     | NULL     |
+----+--------+---------+------+------+----------+----------+

Which seems to be the correct results for a full outer join. if I have duplicate values in A or B, this query returns those duplicates since the distinct in the UNION is not occurring as we are doing a union all; not a union.

(SELECT a.cap_id, b.aqua_id, a.yr_a, b.yr_b, a.qty_a, b.qty_b
FROM tbl_A AS a 
LEFT JOIN tbl_B AS b
  ON a.yr_a = b.yr_b 
 AND a.iso_a = b.iso_b
 AND a.area_a = b.area_b
 AND a.sp_a = b.sp_b
WHERE a.yr_a = 2015
  and (a.yr_a is not null 
   or a.iso_a is not null 
   or a.area_a is not null 
   or a.sp_a is not null))
UNION ALL
(SELECT a.cap_id, b.aqua_id, a.yr_a, b.yr_b, a.qty_a, b.qty_b
FROM tbl_A AS a 
RIGHT JOIN tbl_B AS b
   ON a.yr_a = b.yr_b 
  AND a.iso_a = b.iso_b
  AND a.area_a = b.area_b
  AND a.sp_a = b.sp_b
WHERE b.yr_b = 2015
  and (b.yr_b is not null 
   or b.iso_b is not null 
   or b.area_b is not null 
   or b.sp_b is not null)
 and a.iso_a is null #to exclude extra nulls duplicated by union all.
 );