Explain UNION, MINUS, UNION ALL and INTERSECT


Answer posted by Scott on 2005-05-25 18:32:51: UNION - the values of the first query are returned with the values of the second query eliminating duplicates. 
MINUS - the values of the first query are returned with duplicates values of the second query removed from the first query. 
UNION ALL - the values of both queries are returned including all duplicates 
INTERSECT - only the duplicate values are returned from both queries.

Editorial / Best Answer

rampratap409  

  • Member Since Sep-2006 | Dec 7th, 2006


UNION: Take the common record once( No Duplicate)

UNION ALL : Takes Duplicates also

Minus:  Result of first query - result of second query

examle

select * from emp where rownum<=9;

minus

select * from emp where rownum<9;

this will display 9th row of emp

Intersect:  common result of both query

Showing Answers 1 - 4 of 4 Answers

Scott

  • May 25th, 2005
 

UNION - the values of the first query are returned with the values of the second query eliminating duplicates. 
MINUS - the values of the first query are returned with duplicates values of the second query removed from the first query. 
UNION ALL - the values of both queries are returned including all duplicates 
INTERSECT - only the duplicate values are returned from both queries.

  Was this answer useful?  Yes

Pankaj & Sunil

  • Sep 10th, 2005
 

Union Will eliminate duplicate rows from both the tables which are participating in the union operation

  Was this answer useful?  Yes

Atal

  • Sep 16th, 2005
 

UNION: Take the common record once( No Duplicate)

UNION ALL : Takes Duplicates also

Exampl : Say there is EMP having 14 reconds then...

select count(1) from (select ename from emp)  returns 14

select count(1) from (select ename from emp union  select ename from emp e1) returns 14

 

But

select count(1) from (select ename from emp union All select ename from emp e1)

returns 28.

 

  Was this answer useful?  Yes

UNION: Take the common record once( No Duplicate)

UNION ALL : Takes Duplicates also

Minus:  Result of first query - result of second query

examle

select * from emp where rownum<=9;

minus

select * from emp where rownum<9;

this will display 9th row of emp

Intersect:  common result of both query

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

Answer Question

Click here to Login / Register your free account


 
Send   Reset

 

Related Answered Questions

 

Related Open Questions