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
12965 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

How to create a table interactively?

1093

What are the types of partitions in oracle?

1038

How are extents allocated to a segment?

1126

How to store only time; not date and time?

1020

How do you increase the OS limitation for open files (LINUX and/or Solaris)?

1934

What are transaction isolation levels supported by oracle?

1072

I am using an Oracle 8i Database my data contains Clob data. I am using toad version 7.6 i am able to get the data in toad but unable to extract the data in excel.when trying to extract the data into the excel the toad error says out of memory. Can any body please help me to extract the data through the same toad version. Thanks in advance

2323

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.

2265

What is the minimum client footprint required to connect c# to an oracle database?

1096

How to pass parameters to procedures in oracle?

1042

What are oracle functions?

1031

How to resolve name conflicts between variables and columns?

1031

how to handle exceptions in post production

1427

What is an oracle database table?

1015

Her departmandaki isçilerden empno' su ikinci sirada olan isçilerin empno, deptno, hiredate, sira_no bigilerini döndüren sorguyu yaziniz?

1989