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

Question

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);

I'm guessing you have records where iso_a, yr_a, area_a and sp_a are all null. Does `select * from tbl_A where iso_a is null and yr_a is null and area_a is null and sp_a is null` produce results? what about the same thing but for tbl_b? why invert the order of the joins just make it a right join and everything remains the same? — xQbert, Sep 19 '17 at 17:53

xQbert · Accepted Answer · 2017-09-19T20:46:58.487

0

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.
 );

edited Sep 19 '17 at 20:46

answered Sep 19 '17 at 17:58

xQbert

34,733
2
41
62

tks! The LEFT-UNION_RIGHT didn't give me the FULL OUTER JOIN join I need. It removes duplicates where for e.g cap_id, year, species, iso all match but more than one production type (sample table is simplified from much larger column set). To get around that I used Left-Union-Left suggested here: (@Steve Chambers https://stackoverflow.com/questions/4796872/how-to-do-a-full-outer-join-in-mysql). – sq_peg_rnd_hole Sep 19 '17 at 19:33
@possbil So do you have records where iso_a is null and yr_a is null and area_a is null and sp_a is null? I get the same results in the demo either way; so I must have data missing you have in your tables. – xQbert Sep 19 '17 at 19:47
@possbil the link says to use union all and left /right joins. not left/left. https://www.xaprb.com/blog/2006/05/26/how-to-write-full-outer-join-in-mysql/ and you need to have an exclusion where a.key is null on the second join to eliminate superfluous duplicates on the union all. – xQbert Sep 19 '17 at 19:51
explanation of LEFT-UNION-LEFT further down in the link. I've modified the sample tables to try to show scenario. A LEFT-UNION-RIGHT removes id 3 in Tbl_C. Tbl_a and Tbl_B do not have any NULLS for the columns used in the ON clause. Do occur in Tbl_C result (obviously :)) – sq_peg_rnd_hole Sep 19 '17 at 19:58
Might be the data Tble_A = 900,00 rows Tbl_B = 86,000 rows. But suspect error is human :) – sq_peg_rnd_hole Sep 19 '17 at 20:21
What duplicate value? the capID 45? no. A full outer join given the sample data wouldn't keep it either. so: http://rextester.com/edit/AWDYA21027 shows 6 records capID 3,20,45 and 678 and two NULLS. The two nulls have qaua_ID 78 and 987. – xQbert Sep 19 '17 at 20:40
Updated answer with more info: I think the query is producing the same results as a full outer join. – xQbert Sep 19 '17 at 20:47
Updated answer with more info: I think the query is producing the same results as a full outer join; but I still don't know where your null values are coming from. All I can see is that you must have records for which `a.yr_a is null and a.iso_a is null and a.area_a is null and a.sp_a is null` – xQbert Sep 19 '17 at 20:55
I've generated test tbl and tried to run your query on the db data to see if it fixes problem. I keep getting syntax error. Doesn't seem to like the parenthesis? when I add an insert cmd. Any suggestions?. I'm new to mysql (obviously) and can't see anything un-toward – sq_peg_rnd_hole Sep 19 '17 at 22:05
The query runs fine on rextester once I substitute the tbl_A and tbl_B for the similar tables. I would need to know what the syntax error is. and you say you created a test table (more than one?) did you update the tables in the query? rextester is using a mysql database so the syntax between it and yours should be similar enough to run. – xQbert Sep 19 '17 at 23:17
The SELECT-JOIN query runs fine in phpMyAdmin. But I cannot interrogate the date from the temp table generated. When I add the INSERT INTO (...) statement I get a syntax error: "Unexpected token. (near ()" The red highlight is on the 1st open parenthesis on the 1st SELECT statement. The test table f_tester is empty. Created using the structure of the source table. – sq_peg_rnd_hole Sep 19 '17 at 23:32
So the query works fine without the insert into now? – xQbert Sep 19 '17 at 23:38
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/154834/discussion-between-possbil-and-xqbert). – sq_peg_rnd_hole Sep 19 '17 at 23:42

sq_peg_rnd_hole · Answer 2 · 2017-09-20T18:47:35.363

0

NULLS were generated because: Without a "IS NULL" in the WHERE clause for one of the ON clause variables in the second LEFT JOIN the INNER JOIN runs twice! Exacerbated by fact that I was left joining a shorter table to a longer one (row_# for Tbl_B < Tbl_A), so no record existed in Tbl_b for all Tbl_A 2015 records.

edited Sep 20 '17 at 18:47

answered Sep 20 '17 at 18:41

sq_peg_rnd_hole

73
1
7

score 0 · Answer 3 · answered Sep 20 '17 at 18:44

The answer seems to be quite simple. If you take the 1st select and run it:

SELECT a.*, 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

you should probably get 5 result lines as 5 lines are of the Tbl_A. The problem is that not all of these lines joins with lines in the Tbl_B due to the join criteria. As such when you join these tables, for some rows of Tbl_A, there will be columns of Tbl_B that will be NULL as the join is Outer.

The same applies to your second Query too:

SELECT a.*, b.*
FROM  Tbl_B AS b LEFT JOIN Tbl_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

Rows from Tbl_B do not have an exact join with Tbl_A and as such since you left join to B, you will have lines of the Tbl_B that have plenty of NULLs.

I would advice you to run them 1 by one and see the results of each query.

Thanks ekalyvio!. Yes just learning MySQL. I could not get my head around the IS NULL is NOT NULL in the same WHERE clause. The penny finally dropped. Always a good thing to learn from own brain fart :). — sq_peg_rnd_hole, Sep 20 '17 at 18:57

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

3 Answers3