Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Union vs Union All

Saeed HassanpourJan 14 2016 — edited Jan 16 2016

Dear,

Some years ago, I read the article about "Union and Union all"  that explained

Union is => DISTINCT Q1 WITH DISTINCT(NOT NULL Q2)

Union All  is => Q1 SUM (NOT NULL Q2)

So if you agree about above sentences and spatially these words "NOT NULL", please tell me about your viewpoint.

Regards,

Saeed.

Comments

Jiri.Machotka-Oracle Jan 14 2016

Take a look at this explanation: http://stackoverflow.com/questions/49925/what-is-the-difference-between-union-and-union-all

I guess it's more comprehensive.

unknown-7404 Jan 14 2016 — edited on Jan 14 2016

Union is => DISTINCT Q1 WITH DISTINCT(NOT NULL Q2)

Union All  is => Q1 SUM (NOT NULL Q2)

So if you agree about above sentences and spatially these words "NOT NULL", please tell me about your viewpoint.

I suggest you take Oracle's viewpoint,.

https://docs.oracle.com/cd/B28359_01/server.111/b28286/queries004.htm

UNION ALL Example The UNION operator returns only distinct rows that appear in either result, while the UNIONALL operator returns all rows. The UNIONALL operator does not eliminate duplicate selected rows:

The UNION and UNION ALL operators have NOTHING to do with 'SUM' or 'NOT NULL'.

Jiri.Machotka-Oracle Jan 14 2016

Well, in the set theory the union operation is sometimes expressed as +, so one could use: UNION = DISTINCT (Q1 + Q2), UNION ALL = DISTINCT (Q1) + DISTINCT (Q2)

Union with Nulls (empty set) is like adding zero in arithmetics.

Paulzip Jan 14 2016 — edited on Jan 14 2016

Jiri.Machotka-Oracle wrote:

Well, in the set theory the union operation is sometimes expressed as +, so one could use: UNION = DISTINCT (Q1 + Q2), UNION ALL = DISTINCT (Q1) + DISTINCT (Q2)

Union with Nulls (empty set) is like adding zero in arithmetics.

UNION = DISTINCT(DISTINCT (Q1) + DISTINCT (Q2)) = DISTINCT(Q1 + Q2)

UNION ALL = Q1 + Q2

Jiri.Machotka-Oracle Jan 14 2016

UNION should remove duplicates, shouldn't it?

This shows why such a symbolic expression is quite useless - you never know what it means unless you have detailed definition of what the symbol means. I'd stick to the narrative form.

Saeed Hassanpour Jan 14 2016

Thank you for response, but I have read most of articles about what you and Jiri mention it. So I want to know about intention of writer.

I addition, I guess when we want to use third party like ".NET or JAVA" for execute query on two tables that have null record with "Union" syntax then error occurs.

Paulzip Jan 14 2016

UNION as effectively a set operator (doesn't allow duplicates values)

UNION ALL is effectively a multiset operator (allows duplicates)



Paulzip Jan 14 2016 — edited on Jan 14 2016

I often get annoyed that DBs don't natively support all of the correct set and multiset operators, they can be achieved, but not with simple declarations.

The full list is....

UNION ALL [SUPPORTED]

UNION (DISTINCT) [SUPPORTED]

MINUS ALL [NOT SUPPORTED]

MINUS (DISTINCT) [SUPPORTED]

INTERSECT ALL [NOT SUPPORTED]

INTERSECT (DISTINCT) [SUPPORTED]

Funnily enough though, Oracle's MULTISET functionality does support them all (MINUS is redefined as EXCEPT)!  Weird...

unknown-7404 Jan 14 2016

I addition, I guess when we want to use third party like ".NET or JAVA" for execute query on two tables that have null record with "Union" syntax then error occurs.

I don't know what you mean.

The database executes queries - not .net of Java.

The database does NOT know a query came from .net or Java so can NOT execute it differently.

If you got a syntax error using one of those then you need to fix the syntax error.

But it has NOTHING to do with Oracle or the DB.

Jiri.Machotka-Oracle Jan 14 2016 — edited on Jan 14 2016

Saeed Hassanpour wrote:

for execute query on two tables that have null record with "Union" syntax then error occurs.

This can be related to special treatment of Nulls in the database, which can be compared to 'don't know'. E.g. sum of numbers and a null is null.

Can you give examples of data and queries that didn't work for you? I'd believe there should be now issue with UNION, but let's see.

Saeed Hassanpour Jan 15 2016

Personally I don't have a problem about execute query from out of database, in this discussion only I want to know the means of "DISTINCT Q1 WITH DISTINCT(NOT NULL Q2)" so the first thing that came to my mind may be can problem when execute query from out of database somehow but rp048 said:

The database does NOT know a query came from .net or Java so can NOT execute it differently.

Anyway, please attention below code

select 1 from dual

union

select null from dual

result:

1

-

it means this sentence is wrong :"DISTINCT Q1 WITH DISTINCT(NOT NULL Q2)" because null record is include of result and the correct sentence is "DISTINCT (Q1) + DISTINCT (Q2)".


3150692 Jan 15 2016 — edited on Jan 15 2016

Hi

   You are highlighting the concept is applicable for NOT NULL for Union

" Union is => DISTINCT Q1 WITH DISTINCT(NOT NULL Q2) "

Actually even  Nullable row is treated as a duplicate and the same is removed when it comes to Union

ex : The below gives only 1 row it means it is considering two nulls as single row by removing the duplicates

SQL> SELECT COUNT(*) FROM (SELECT NULL FROM DUAL UNION SELECT NULL FROM DUAL)
  2  /

  COUNT(*)
----------
         1

garani hv

Jiri.Machotka-Oracle Jan 15 2016 — edited on Jan 15 2016

Check these two:

SQL>  SELECT COUNT(x) FROM (SELECT null as x FROM DUAL UNION ALL SELECT NULL as

x FROM DUAL);

  COUNT(X)

----------

         0

SQL>  SELECT COUNT(*) FROM (SELECT NULL FROM DUAL UNION ALL SELECT NULL FROM DUA

L);

  COUNT(*)

----------

         2

The former counts values of x in the result set - nulls are excluded, the latter counts rows.

3150692 Jan 15 2016 — edited on Jan 15 2016

Jiri

  I think this is a different subject

Basically you are trying to explain difference between count(col_name) Vs count(*)

By default count(col_name) excludes the null value while counting the rows

Regards

garani hv


RamilR Jan 15 2016 — edited on Jan 15 2016

Hi,

UNION ALL just adds one set to another.

UNION does the same, but additionally removes duplicates (as if you would run DISTINCT for the result set) and orders final result alphabetically.

Basically if you do not need duplicate values to be excluded and ordering to be made on final result set, then use UNION ALL. It is less expensive.

About your example, I would state it like below, if I got your point correct. NOT NULL has nothing to do with it.

Union is => DISTINCT (Q1 WITH Q2)

Union All  is => Q1 WITH Q2

unknown-7404 Jan 15 2016 — edited on Jan 15 2016
UNION does the same, but additionally removes duplicates (as if you would run DISTINCT for the result set) and orders final result alphabetically.

NO - it does NOT order the results.

If you want the result set in a specific order you need to use an ORDER BY clause.

William Robertson Jan 16 2016 — edited on Jan 16 2016

Saeed Hassanpour wrote:

I addition, I guess when we want to use third party like ".NET or JAVA" for execute query on two tables that have null record with "Union" syntax then error occurs.

No error occurs just because .Net or Java performed a union of two tables with a null record, whatever that is. Please start a new thread with the exact error message and a reproducible example.

1 - 17
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Feb 13 2016
Added on Jan 14 2016
17 comments
3,523 views