Union,Intersection,Minus

UNION CLAUSE
            Multiple queries can be put together and their output combined using the union clause. The union clause merges the output of two or more queries into a single set of rows and columns. The output will be

• Records only in query one + Records only in query two + A single set of records which is common in both queries.

Restriction on using UNION clause

• Number of columns in all the queries should be the same
• The datatype of the columns in each query must be same
• Unions cannot be used in subqueries.
• Aggregate functions cannot be used with union clause.

Syntax:-  select columslist from tablename union select columns list from tablename.
Ex:-     1) select * from vision union select * from vision2;
2) select eno,sal from vision union select eno,sal from vision3 ;
3) select eno,sal from vision where sal>2000 union select eno,sal
from vision3 where sal<2000;
4) select eno,sal from vision where sal>2000 union select eno,sal

from vision3 where sal<2000 union select eno,sal from vision2;

UNION ALL
            The union all returns all rows selected by either query including duplicates. The following example combine the result with the union all operator which does not eliminate duplicate rows.
Syntax:-  select columslist from tablename UNION ALL select columns list from         tablename.

Ex:- select eno,sal from vision union all select eno,sal from vision3;

INTERSECT
            Multiple queries can be put together and their output combined using the intersect clause. The intersect clause outputs only rows produced by both queries intersected i.e. the output in an intersect clause will include only those rows that are retrieved by both the queries. The output will be

• A single set of records which are common in both queries.

Syntax:-  select columslist from tablename INTERSECT select columns list from         tablename.
Ex:-     1)select * from vision intersect select * from vision2;
2)select eno,sal from vision intersect select eno,sal from vision3;
3)select eno,sal from vision intersect select eno,sal
from vision3;
4)select eno,sal from vision where sal>2000 intersect select eno,sal
from vision3 where sal<2000 intersect select eno,sal from vision2;
5)select eno,sal from vision where sal>2000 intersect select eno,sal

from vision3 where sal<2000 union select eno,sal from vision2;

MINUS CLAUSE
            Multiple queries can be put together and their output combined using then minus clause. The minus clause outputs the rows produced by the first query after filtering the rows retrieved by the second query. The output will be

• Only records in first query.

Syn:- select colname from tablename minus select colname from tablename
Ex:-   select eno from vision minus select eno from vision2;

For
Online Classes

Contact Us: +919885348743