SO46306405, MySQL - rextester

Language: Layout:

#MySQL 5.7.12
#please drop objects you've created at the end of the script 
#or check for their existance before creating
#'\\' is a delimiter

drop table if exists SO46306405_tbl_A;
drop table if exists SO46306405_tbl_B;

Create table SO46306405_tbl_A (
    cap_Id int,
    yr_A int,
    sp_a varchar(3),
    iso_a int,
    area_a int,
    qty_a numeric(10,2));
 
 Create table SO46306405_tbl_B (
    aqua_Id int,
    yr_b int,
    sp_b varchar(3),
    iso_b int,
    area_b int,
    qty_b numeric(10,2));
    
INSERT INTO SO46306405_tbl_A values(3, 2015,  'TRR',    54,      8,   120);
INSERT INTO SO46306405_tbl_A values(678, 2015,  'BOM',    62,     27,   0.0);
INSERT INTO SO46306405_tbl_A values(20, 2015,  'TRR',    54,     27,   0.0);
INSERT INTO SO46306405_tbl_A values(45, 2015,  'FRC',     7,     15, 86800);

INSERT INTO SO46306405_tbl_B values(78, 2015,  'OTS',    32,     27,  6868);
INSERT INTO SO46306405_tbl_B values(333, 2015,  'FRC',     7,     15,   550);
INSERT INTO SO46306405_tbl_B values(789, 2015,  'TRR',    54,     27, 45000);
INSERT INTO SO46306405_tbl_B values(987, 2015,  'TRR',    32,     27,    40);

(SELECT a.cap_id, b.aqua_id, a.yr_a, b.yr_b, a.qty_a, b.qty_b
FROM SO46306405_tbl_A AS a LEFT JOIN SO46306405_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_b
FROM  SO46306405_tbl_B AS b LEFT JOIN SO46306405_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);

drop table if exists SO46306405_tbl_A;
drop table if exists SO46306405_tbl_B;


absolute service time: 0,36 sec