what is the difference between UNION AND UNIONALL
- 4 Answers
- 11493 Views
- I also Faced
- E-Mail Answers
Answers were Sorted based on User's Feedback
Answer / k
UNION provides only distinct values as output whereas UNION
ALL provides all values.
So UNION ALL seems to be faster than UNION.
| Is This Answer Correct ? | 25 Yes | 1 No |
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 |
Answer / vaithianathan
union displays only different values in the multiple table.
but union all displays all related values.
| Is This Answer Correct ? | 1 Yes | 0 No |
Answer / krishna kant kumar
The UNION operator returns all rows from two or multiple tables and eliminates any duplicate rows but the UNION ALL operator returns from both queries, including all duplications.
The UNION operator can use DISTINCT keyword but the UNION ALL cannot it.
| Is This Answer Correct ? | 0 Yes | 0 No |
How to create a stored function in oracle?
Give syntax for SQL and ORACLE joins.
What is a read write transaction in oracle?
i have procedure p1.and also i declare the same procedure in a package. whice one is efficient package procedure or procedure?
Is oracle a relational database?
What are the oracle differences between nvl and coalesce
State and explain about oracle instance?
If server is in US and client is in india there is timezone is diffrence, How can we display date in indian time when data is displayed from US server timezone?
What is a proxy class?
> CREATE OR REPLACE FUNCTION FACTORIAL_1(factstr varchar2 ) 2 RETURN NUMBER AS 3 new_str VARCHAR2(4000) := factstr||'*' ; 4 fact number := 1 ; 5 BEGIN 6 7 WHILE new_str IS NOT NULL 8 LOOP 9 fact := fact * TO_NUMBER(SUBSTR(new_str,1,INSTR(new_str,'*')-1)); 10 new_str := substr( new_str,INSTR(new_str,'*')+1); 11 END LOOP; 12 13 RETURN fact; 14 15 END; explanation Above program?
How to use "in out" parameter properly?
Difference between primary key and unique key ?
77 Answers Accenture, B2B Software Technologies, Cognizant, HP Finsoft, IndiaNIC, Karomi Technology, Keane India Ltd, L&T, Onward eServices, R Systems, Shakti, Techtic Solutions,
Oracle General 
