While executing the below two queries (focus on the part between two asterisks * ____ *), I am really wondering how does the position of UNION ALL changes the output. I am unable to understand.
Query 1
SELECT 'Jack' AS Name, 100 AS Marks FROM DUAL
*UNION All SELECT 'Jack' AS Name, 100 AS Marks FROM DUAL*
UNION SELECT 'Jack' AS Name, 100 AS Marks FROM DUAL
Query Result
NAME MARKS
Jack 100
Query 2
SELECT 'Jack' AS Name, 100 AS Marks FROM DUAL
UNION SELECT 'Jack' AS Name, 100 AS Marks FROM DUAL
*UNION ALL SELECT 'Jack' AS Name, 100 AS Marks FROM DUAL*
Query Result
NAME MARKS
Jack 100
Jack 100
The difference between Union and Union all is that Union all will not eliminate duplicate rows,
first query output:
step 1:
SELECT 'Jack' AS Name, 100 AS Marks FROM DUAL
UNION All SELECT 'Jack' AS Name, 100 AS Marks FROM DUAL
result 2 rows because union all allows duplicates.
step 2:
UNION SELECT 'Jack' AS Name, 100 AS Marks FROM DUAL
this query will pick only row without duplicates from the above 2 rows and itself. returns 1 row.
in the second query...
step 1
SELECT 'Jack' AS Name, 100 AS Marks FROM DUAL
UNION SELECT 'Jack' AS Name, 100 AS Marks FROM DUAL
returns 1 row because union selects only distinct rows.
step 2
UNION ALL SELECT 'Jack' AS Name, 100 AS Marks FROM DUAL
return 2 rows because it allows duplicates.