Eliminating NULL values from the UNION set operators

Question

How should we need to eliminate NULL values rather than keeping it in separate rows, values must need to reside in a column, thus by making the NULL values to disappear, by making use of a union set operator from the executed result sets?

Consider the simple SQL query

SELECT
  *
FROM
  (
    (SELECT
      6 + 2 AS addition,
      NULL AS subtraction,
      NULL AS multiplication,
      NULL AS division
    FROM
      DUAL)
    UNION
    (SELECT
      NULL AS addition,
      6 - 2 AS subtraction,
      NULL AS multiplication,
      NULL AS division
    FROM
      DUAL)
    UNION
    (SELECT
      NULL AS addition,
      NULL AS subtraction,
      6 * 2 AS multiplication,
      NULL AS division
    FROM
      DUAL)
    UNION
    (SELECT
      NULL AS addition,
      NULL AS subtraction,
      NULL AS multiplication,
      6 / 2 AS division
    FROM
      DUAL)
  ) A;

Actual Result:

+----------+-------------+----------------+----------+
| addition | subtraction | multiplication | division |
+----------+-------------+----------------+----------+
|        8 |        NULL |          NULL  |     NULL |
|     NULL |           4 |          NULL  |     NULL |
|     NULL |        NULL |            12  |     NULL |
|     NULL |        NULL |          NULL  |   3.0000 |
+----------+-------------+----------------+----------+
4 rows in set (0.00 sec)

Expected Result:

+----------+-------------+----------------+----------+
| addition | subtraction | multiplication | division |
+----------+-------------+----------------+----------+
|        8 |           4 |          12    |   3.0000 |
+----------+-------------+----------------+----------+
1 row in set (0.00 sec)

---

SQL Demo

Answer 1

You can try below -

SELECT
  max(addition) as addition,max(subtraction) as subtraction, max(multiplication) as multiplication,max(division) as division
FROM
  (
    (SELECT
      6 + 2 AS addition,
      NULL AS subtraction,
      NULL AS multiplication,
      NULL AS division
    FROM
      DUAL)
    UNION
    (SELECT
      NULL AS addition,
      6 - 2 AS subtraction,
      NULL AS multiplication,
      NULL AS division
    FROM
      DUAL)
    UNION
    (SELECT
      NULL AS addition,
      NULL AS subtraction,
      6 * 2 AS multiplication,
      NULL AS division
    FROM
      DUAL)
    UNION
    (SELECT
      NULL AS addition,
      NULL AS subtraction,
      NULL AS multiplication,
      6 / 2 AS division
    FROM
      DUAL)
  ) A;