UNION と UNION ALL の違い

SQL>           SELECT 1 AS STRING FROM DUAL
  2  UNION     SELECT 1 AS STRING FROM DUAL
  3  UNION     SELECT 2 AS STRING FROM DUAL
  4  UNION     SELECT 3 AS STRING FROM DUAL
  5  UNION     SELECT 4 AS STRING FROM DUAL ;

       STRING
-------------
            1  ※
            2
            3
            4

※と同じ値のSELECT結果が1行にだけに。

SQL>           SELECT 1 AS STRING FROM DUAL
  2  UNION ALL SELECT 1 AS STRING FROM DUAL
  3  UNION ALL SELECT 2 AS STRING FROM DUAL
  4  UNION ALL SELECT 3 AS STRING FROM DUAL
  5  UNION ALL SELECT 4 AS STRING FROM DUAL ;

       STRING
-------------
            1
            1
            2
            3
            4

UNION ALL は同じ値でもそのままSELECTされる。