Implementation of Queries using SQL Set operators: Union, union all,
Intersect, Minus
1) UNION Clause
The UNION clause merges or combines the output of two or more queries into a single
set of rows and columns
Multiple queries can be put together and their output combined using the UNION clause.
The output of both queries will be displayed as above.
o Output : Record only in query one + records only in query two + a single set of
records which is common in both queries.
Syntax: SELECT * FROM table1 UNION SELECT * FROM table2;
Example:
Select salesman_no ‘ID’, name from salesman_master Where city=‘bombay‘ UNION
Select client_no ‘ID’, name from client_master Where city=‘bombay‘;
Restriction on UNION clause:
The number of column in all the queries should be same.
The data type of columns in each query should be same
Unions cannot be used in Sub queries
Union cannot be used with aggregate functions
2) UNION ALL Clause
Syntax: SELECT * FROM table1 UNION ALL SELECT * FROM table2;
3) INTERSECT Clause
The INTERSECT clause outputs only those rows produced by both queries intersected.
The output of INTERSECT clause will include only those rows that are retrieved by both
the queries
4) UNION ALL Clause
Syntax: SELECT * FROM table1 UNION ALL SELECT * FROM table2;
5) INTERSECT Clause
The INTERSECT clause outputs only those rows produced by both queries intersected.
The output of INTERSECT clause will include only those rows that are retrieved by both
the queries
The output of both queries will be displayed as above
The final output of INTERSECT clause will be:
A single set of Records which is common in both queries.
SELECT * FROM table1 INTERSECT SELECT * FROM table2;
Example:
Select sman_no, name from salesman_master where city='bombay' INTERSECT
Select salesman_master. sman_no, name from salesman_master, sales_order where
salesman_master. sman_no=sales_order. sman_no;
Output:
name Sman_no
Kiran S00001
Ravi S00003
6) MINUS Clause
The MINUS clause outputs the rows produced by the first query, after filtering the rows
retrieved by the second query.
The output of both queries will be displayed as above
The final output of MINUS clause will be:
Output: Records only in query one
Example: Retrieve all the product number of non-moving items from the Product_Master
table.
SELECT * FROM table1 MINUS SELECT * FROM table2;
Table Name: sales_order_details
Product_no Order_no
P00001 O19001
P00002 O19002
P00003 O19003
P00004 O19004
Table Name: Product_Master
Description Product_no
floppies P00001
Monitors P00002
Mouse P00003
HDD P00007
1.44 drive P00008
Select product_no from Product_Master MINUS
Select product_no from sales_order_details;
Output:
Product_no
P00007
P00008
