SQL Set Operators

Set Operator Types

Union	This will combine the records of multiple tables having the same structure with out duplicates
Union all	This will combine the records of multiple tables having the same structure but including duplicates.
Intersect	This will give the common records of multiple tables having the same structure.
Minus	This will give the records of a table whose records are not in other tables having the same structure.

 
Take examples from Student1 and Student2 tables 
 
Select * from Student1;

SrNo	Name
101	Name101
102	Name102

 

Select * from Student2;
 

SrNo	Name
102	Name102
103	Name103

 



UNION Operator

The UNION operator returns all rows selected by either query. Use the UNION operator to return all rows from multiple tables and eliminate any duplicate rows.

   

Guidelines

•         The number of columns and the datatypes of the columns being selected must be identical in all the SELECT statements used in the query. The names of the columns need not be identical.

•         UNION operates over all of the columns being selected.

•         NULL values are not ignored during duplicate checking.

•         The IN operator has a higher precedence than the UNION operator.

•         By default, the output is sorted in ascending order of the first column of the SELECT clause.

Using the UNION SET Operator 

The UNION operator eliminates any duplicate records. If there are records that occur both in the  Student1 and the Studnet2 tables and are identical, the records will be displayed only once.

Consider the following example:

SQL>SELECT * FROM student1

          UNION

          SELECT * FROM student2;

Output:

SrNo	Name
101	Name101
102	Name102
103	Name103

 

UNION ALL Operator

This will combine the records of multiple tables having the same structure but including duplicates.

Use the UNION ALL operator to return all rows from multiple queries. 

Guidelines

•         Unlike UNION, duplicate rows are not eliminated and the output is not sorted by default.

•         The DISTINCT keyword cannot be used.

Note: With the exception of the above, the guidelines for UNION and UNION ALL are the same.

Using the UNION ALL SET Operator 

The UNION ALL operator returns all records along with duplicate records.

Consider the following example:

SQL>SELECT * FROM student1

          UNION ALL

          SELECT * FROM student2;

Output:

SrNo	Name
101	Name101
102	Name102
102	Nmae102
103	Name103

 








INTERSECT Operator


Use the INTERSECT operator to return all rows common to multiple queries.

 

Guidelines

•         The number of columns and the datatypes of the columns being selected by the SELECT statements in the queries must be identical in all the SELECT statements used in the query. The names of the columns need not be identical.

•         Reversing the order of the intersected tables does not alter the result.

•         INTERSECT does not ignore NULL values.

 

Using the INTERSECT Operator 

The INTERSECT operator returns common records from student tables srno102 record.

Consider the following example:

SQL>SELECT * FROM student1

          INTERSECT

          SELECT * FROM student2;

         

Output:

SrNo	Name
102	Name102

MINUS Operator 

Use the MINUS operator to return rows returned by the first query that are not present in the second query (the first SELECT statement MINUS the second SELECT statement).

Guidelines

•         The number of columns and the datatypes of the columns being selected by the SELECT statements in the queries must be identical in all the SELECT statements used in the query. The names of the columns need not be identical.

•         All of the columns in the WHERE clause must be in the SELECT clause for the MINUS operator to work.

Using the MINUS Operator 

The MINUS operator returns rows returned by the first query from student1.

Consider the following example:

SQL>SELECT * FROM student1

          MINUS

          SELECT * FROM student2;

Output:

SrNo	Name
101	Name101
102	Name102

  

SET Operator Guidelines

•         The expressions in the SELECT lists must match in number and data type.

•         Parentheses can be used to alter the sequence of execution.

•         The ORDER BY clause:

–        Can appear only at the very end of the statement

–        Will accept the column name, aliases from the first SELECT statement, or the positional notation

Order by in set Operators Example:

         SELECT * FROM student1

          UNION

          SELECT * FROM student2

          ORDER BY SrNo;