UNION The UNION command is used to select related information from two t

what is the difference between UNION AND UNIONALL

Question Posted / vinod singh kushwah

4 Answers
12902 Views
I also Faced
E-Mail Answers

UNION
The UNION command is used to select related information
from two tables, much like the JOIN command. However, when
using the UNION command all selected columns need to be of
the same data type.

Note: With UNION, only distinct values are selected.

SQL Statement 1
UNION
SQL Statement 2

Employees_Norway:

E_ID E_Name
01 Hansen, Ola
02 Svendson, Tove
03 Svendson, Stephen
04 Pettersen, Kari

Employees_USA:

E_ID E_Name
01 Turner, Sally
02 Kent, Clark
03 Svendson, Stephen
04 Scott, Stephen

------------------------------------------------------------
--------------------

Using the UNION Command
Example
List all different employee names in Norway and USA:

SELECT E_Name FROM Employees_Norway
UNION
SELECT E_Name FROM Employees_USA

Result

E_Name
Hansen, Ola
Svendson, Tove
Svendson, Stephen
Pettersen, Kari
Turner, Sally
Kent, Clark
Scott, Stephen

Note: This command cannot be used to list all employees in
Norway and USA. In the example above we have two employees
with equal names, and only one of them is listed. The UNION
command only selects distinct values.

UNION ALL
The UNION ALL command is equal to the UNION command, except
that UNION ALL selects all values.

SQL Statement 1
UNION ALL
SQL Statement 2

------------------------------------------------------------
--------------------

Using the UNION ALL Command
Example
List all employees in Norway and USA:

SELECT E_Name FROM Employees_Norway
UNION ALL
SELECT E_Name FROM Employees_USA

Result

E_Name
Hansen, Ola
Svendson, Tove
Svendson, Stephen
Pettersen, Kari
Turner, Sally
Kent, Clark
Svendson, Stephen
Scott, Stephen

Is This Answer Correct ?

20 Yes

0 No

Post New Answer View All Answers

Please Help Members By Posting Answers For Below Questions

What types of joins are used in writing subqueries?

966

What happens if recursive calls get out of control?

1060

How to load data from external tables to regular tables?

1041

How view is different from a table?

1017

How will you identify oracle database software release?

987

What are the execution control statements in oracle?

1068

what are archived logs?

2182

Point the difference between translate and replace?

1068

How to assign a tablespace to a users in oracle?

1029

hello friends Im doing my final year engineering in B.Tech.. one of uncle said he can provide job in his company if im good in database management. but i have only basic knowledge about database, so like to join database management course in good intuition. so friends kindly help me to get good intuition because its my future.

2245

What are the uses of synonyms?

1129

What is oracle used for?

1007

Explain enable novalidate constraint.

1064

What is connection pooling in oracle?

949

WHAT IS ecc 6.0

2139