How to correctly combine multiple WHERE statements and multiple ON clauses when emulating a FULL OUTER JOIN in MySQL

Question

I have two tables. Global fishing data (capture & aquaculture). I want to combine by emulating FULL OUTER JOIN in MySQL.

Tbl_A - capture

• capture_id
• year_c
• species_c
• iso_code_c
• area_code_c
• environ_code_c
• qty_taken
• value_c
• symbol_c

Tbl_B - aquaculture

• aqua_id
• year_a
• species_a
• iso_code_a
• area_code_a
• environ_code_a
• qty_prod
• value_aqua
• symbol_a

This is complicated by:

• Tbl_A and Tbl_B are not related
• Tbl_B may not have a corresponding row

Tbl_A - capture

cap_id| yr_c| sp_c| iso_c| area_c| qty_c
     3| 2015|  TRR|    54|      8|   120
   678| 2015|  BOM|    62|     27|   0.0
    20| 2015|  TRR|    54|     27|   0.0
    45| 2015|  FRC|     7|     15| 86800

Tbl_B - aquaculture

cap_id| yr_a| sp_a| iso_a| area_a| qty_a
    78| 2015|  OTS|    32|     27|  6868
   333| 2015|  FRC|     7|     15|   550
   789| 2015|  TRR|    54|     27| 45000
   987| 2015|  TRR|    32|     27|    40

For a selected year (2015) I am trying to capture:

1. Tbl_A & Tbl_B records where (species, iso_code, area) are the same;
2. Tbl_A records (species, iso_code, area) that don't have a Tbl_B match; and
3. Tbl_B records (species, iso_code, area) that don't have a Tbl_A match.

Tbl_C - Desired Final Tbl

id| cap_id| aqua_id| yr_c| yr_a| sp_c| sp_a| iso_c| iso_a|area_c|area_a| qty_c| qty_a 

1 |     20|     789| 2015| 2015|  TRR|  TRR|    54|    54|    27|    27|   0.0| 45000
2 |     45|     333| 2015| 2015|  FRC|  FRC|     7|     7|    15|    15| 86800| 550
3 |    678|    NULL| 2015| NULL|  BOM| NULL|    62|  NULL|    27|  NULL|   0.0| NULL
4 |      3|    NULL| 2015| NULL|  TRR| NULL|    54|  NULL|     8|  NULL|   120| NULL
5 |   NULL|      78| NULL| 2015| NULL|  OTS|  NULL|    32|  NULL|    27|  NULL| 6868    
6 |   NULL|     987| NULL| 2015| NULL|  TRR|  NULL|    32|  NULL|    27|  NULL| 40

I have a query that uses a UNION to JOIN two LEFT JOINS:

(SELECT 
c.capture_id,
a.aqua_id,
c.year_c,
a.year_a,
c.species_c,
a.species_a,
c.iso_code_c,
a.iso_code_a,
c.area_c,
a.area_a,
c.environ_code_c,
a.environ_code_a,
c.qty_taken,
a.qty_prod
FROM capture AS c 
LEFT JOIN aquaculture AS a 
ON c.year_c = a.year_a AND c.iso_code_c = a.iso_code_a AND c.area_c = a.area_a AND c.species_c = 
a.species_a
WHERE c.year_c = 2015 AND a.year_a = 2015)
UNION
(SELECT 
c.capture_id,
a.aqua_id,
c.year_c,
a.year_a,
c.species_c,
a.species_a,
c.iso_code_c,
a.iso_code_a,
c.area_c,
a.area_a,
c.qty_taken,
a.qty_prod
FROM  aquaculture AS a
LEFT JOIN capture AS c 
ON c.year_c = a.year_a AND c.iso_code_c = a.iso_code_a AND c.area_c = a.area_a AND c.species_c = a.species_a
WHERE a.year_a = 2015 AND c.year_c = 2015);

But the query above is only returning a small sub-set of matched records

id| cap_id| aqua_id| yr_c| yr_a| sp_c| sp_a| iso_c| iso_a|area_c|area_a| qty_c| qty_a 

1 |     20|     789| 2015| 2015|  TRR|  TRR|    54|    54|    27|    27|   0.0| 45000
2 |     45|     333| 2015| 2015|  FRC|  FRC|     7|     7|    15|    15| 86800|   550

I do not understand how I am cancelling out the effect of the LEFT JOINS

Answer 1

Your problem is in the WHERE clauses. You are excluding the records where the 'RIGHT' table record is null.

SELECT *
FROM c LEFT JOIN a ON ...
WHERE c.year = 2015 /*remove AND a.year = 2015*/
UNION SELECT *
FROM a LEFT JOIN c ON ...
WHERE a.year = 2015 /*remove AND c.year = 2015*/

Answer 2

As MySQL supports only left, right and inner joins - you need to write two queries: 1) table A left join table B 2) table B left join table A. And then combine them into one result with help of UNION. Union will also remove duplicated rows in the result, so you will only have 3 types of rows in result set:

1. Row with data from both tables, i e A inner join B
2. Row with data from table A, where there are no matches from B. i e A left join B
3. Row with data from table B, where there are no matches in A, i e B left join A

Try:

SELECT ...
FROM c
LEFT JOIN a ON ...
UNION
SELECT ...
FROM a
LEFT JOIN c ON ...