MYSQL FULL OUTER JOIN - все результаты NULL при использовании 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 |
Я получил полное внешнее соединение, которое я искал
НО запрос также генерирует целую кучу всех записей NULL (идентификаторы 7-9 в Tbl_C)
Tbl_C - финал 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
Я пытаюсь понять, что вызывает дополнительные кратные все записи NULL?
Используемый запрос был:
(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);
3 ответа
ДЕМО: http://rextester.com/AWDYA21027 используя ваш пример данных и запроса Я не получаю повторяющиеся пустые столбцы. Это подразумевает проблему с данными.
Зачем делать это левым соединением? Просто переключитесь влево и вправо, а пункт where - на b.yr_a.
и чтобы исключить пустые значения, убедитесь, что хотя бы один из критериев соединения соответствует, удалив записи, у которых все 4 значения имеют нулевое значение?
С учетом ваших примеров данных будет возвращено полное внешнее соединение: по заказу cap_Id и 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 | |
+--------+---------+------+------+-------+-------+
Следующий запрос возвращает:
+----+--------+---------+------+------+----------+----------+
| | 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 |
+----+--------+---------+------+------+----------+----------+
Что, кажется, является правильным результатом для полного внешнего соединения. если у меня есть повторяющиеся значения в A или B, этот запрос возвращает эти дубликаты, поскольку отличительные в 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.
);
NULLS были сгенерированы потому, что: без "IS NULL" в предложении WHERE для одной из переменных предложения ON во втором LEFT JOIN INNER JOIN выполняется дважды! Это усугубляется тем фактом, что мне пришлось присоединить более короткую таблицу к более длинной (row_ # для Tbl_B
Ответ кажется довольно простым. Если вы берете 1-й выбор и запускаете его:
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
вероятно, вы должны получить 5 строк результата, так как 5 строк относятся к Tbl_A. Проблема в том, что не все эти строки соединяются со строками в Tbl_B из-за критериев соединения. Таким образом, когда вы присоединяетесь к этим таблицам, для некоторых строк Tbl_A будут столбцы Tbl_B, которые будут иметь значение NULL, поскольку соединение является Outer.
То же самое относится и к вашему второму запросу:
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
Строки из Tbl_B не имеют точного соединения с Tbl_A, и, так как вы оставили соединение с B, у вас будут строки из Tbl_B, которые имеют множество NULL.
Я бы посоветовал вам запустить их один за другим и посмотреть результаты каждого запроса.