2

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

Thanks :)

Ash Upadhyay
  • 1,796
  • 2
  • 15
  • 20
  • 1
    Possible duplicate of [What is the difference between UNION and UNION ALL?](https://stackoverflow.com/questions/49925/what-is-the-difference-between-union-and-union-all) – dfundako Mar 13 '18 at 13:36

4 Answers4

3

If you don't specify parantheses, the selects would get executed one after the other. All set operators minus, union, union all, intersect have the same precedence.

Oracle Documentation

In the first query, UNION is performed at the end so there would be no dup rows per the query. In the second, UNION ALL is performed at the end, so there would be dup rows per your query.

Vamsi Prabhala
  • 48,685
  • 4
  • 36
  • 58
  • Just for clarification, do you mean to say if union is performed at the end, it will remove the duplicates generated by the queries before it? – Ash Upadhyay Mar 13 '18 at 13:52
  • 1
    @AshUpadhyay It is an order of operations thing really. You need to figure out in which order the unions/union alls are happening, then there will be no surprises as to the outcome. – Tim Biegeleisen Mar 13 '18 at 13:52
  • @TimBiegeleisen Thanks :) I am really trying to figure that out :) – Ash Upadhyay Mar 13 '18 at 13:53
1

Both union and union all have the same precedence as operations. So in the absence of parentheses, your two unions would be evaluated from top to bottom. Your first query is being evaluated like this:

SELECT Name, Marks
FROM
(
    SELECT 'Jack' AS Name, 100 AS Marks FROM DUAL 
    UNION All
    SELECT 'Jack' AS Name, 100 AS Marks FROM DUAL
) t
UNION
SELECT 'Jack' AS Name, 100 AS Marks FROM DUAL

The same reasoning applies to your second query.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
1

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.

Sas
  • 278
  • 1
  • 12
  • ok just for more clarification, what do you mean to say by the second query the last union all will include the duplicate row? – Ash Upadhyay Mar 13 '18 at 13:52
0

To understand this behavior,you need to be clear with the difference between UNION and UNION ALL.

Query 1: Output of first two lines will return 2 rows. However, last line(sql statement) with 'UNION' operator will remove duplicate rows,including it's own output. So, total first query will return only 1 row.

Query 2: Now, UNION is on 2nd line. After getting combined with 1st line, it will again return only 1 row. But, on the 3rd line with 'UNION ALL',it will return 2 rows. Because, 'UNION ALL' won't remove duplicates.

Channa
  • 742
  • 17
  • 28