SET Operations in SQL

SQL supports few Set operations which can be performed on the table data. These are used to get meaningful results from data stored in the table, under different conditions. It is used to combine two or more SQL SELECT statements. Many database users find Set Operations to be much easier to use than joins. SQL Joins tends to combine columns from different tables, whereas SQL set operators combine rows from distinct queries.

Now, let us look at different set operations with examples.

Types of Set Operations

There are four different types of Set Operations in SQL as illustrated below.

1. UNION

SQL UNION is used to combine the results of two or more SELECT statements. However, it will eliminate duplicate rows from its resultset. In the case of union, the number of columns and datatype must be the same in both the tables, on which the UNION operation is being applied.

Example: Union of Tables

Consider the two tables below for getting a union.

table1

table2

The below SQL query shows UNION operation.

    SELECT * FROM table1
    UNION
    SELECT * FROM table2;

Output:

Example: UNION in Oracle

The five queries are joined using the UNION operator. The final result set contains the value from all the SQLs. Note that duplicates are removed and data is also sorted.

    SELECT 1 NUM FROM DUAL
    UNION
    SELECT 5 FROM DUAL
    UNION
    SELECT 3 FROM DUAL
    UNION
    SELECT 6 FROM DUAL
    UNION
    SELECT 3 FROM DUAL

Output:

In the case of Oracle, the columns selected in the SELECT statement must be of compatible data type, otherwise, it throws an error message.

    SELECT TO_DATE('12-OCT-03') FROM DUAL
    UNION
    SELECT '13-OCT-03' FROM DUAL

Output:

2. UNION ALL

The UNION ALL operation is similar to the UNION. The main and only difference is that the UNION does not return duplicate rows while UNION ALL shows the duplicate rows.

Example: Union All

Consider the two tables below.

table1

table2

The below SQL query shows UNION ALL operation.

    SELECT * FROM table1
    UNION ALL
    SELECT * FROM table2;

Output:

Example: UNION ALL in Oracle

The below query shows UNION ALL operation. Note that the result set is generated without sorting and deduplication.

    SELECT 1 NUM FROM DUAL
    UNION ALL
    SELECT 5 FROM DUAL
    UNION ALL
    SELECT 3 FROM DUAL
    UNION ALL
    SELECT 6 FROM DUAL
    UNION ALL
    SELECT 3 FROM DUAL

Output:

3. INTERSECT

INTERSECT operation is used to combine two SELECT statements, but it only returns the records which are common from both SELECT statements. In the case of INTERSECT, the number of columns and datatype must be the same. The result set has no duplicates and it arranges the data in ascending order by default.

Note: MySQL does not support INTERSECT operation.

Note: MySQL does not support INTERSECT operation.

Example: Intersect

Consider the two tables below.

table1

table2

The below SQL query shows INTERSECT operation.

    SELECT * FROM table1
    INTERSECT
    SELECT * FROM table2;

Output:

Example: INTERSECT in Oracle

Assuming an employee table, the below SELECT query retrieves the salary which is common in departments 10 and 20.

    SELECT salary
    FROM employees
    WHERE dept_id = 10
    INTERSECT
    SELECT salary
    FROM employees
    WHERE dept_id = 20

4. MINUS

The MINUS operation combines results of two SELECT statements and returns only those in the final result, which belongs to the first set of the result. It has no duplicates and data arranged in ascending order by default.

Oracle is the only database that supports the MINUS operation. For, databases like PostgreSQL, SQL Server we use EXCEPT operator. MySQL database does not support either of the operators. You can perform this operation using Joins in the case of MySQL.

Example: Use MINUS in Oracle

Consider the two tables below.

table1

table2

The below query displays the rows which are present in the first query but absent in the second query, with no duplicates and data arranged in ascending order by default.

    SELECT name FROM table1 
    MINUS
    SELECT name FROM table2;

Output:

Example: Use EXCEPT for PostgreSQL, SQL Server

The SQL EXCEPT operator returns all rows which are present in the first query but absent in the second query.

    SELECT name FROM table1 
    EXCEPT
    SELECT name FROM table2;

Output:

Important Points:

• All SELECT statements must have the same number of columns and column names used in the result set are taken from the first query.
• Data types of the columns must be compatible with the oracle. Oracle does not perform implicit type conversion if corresponding columns in the component queries belong to different data type groups.
• The UNION and INTERSECT operators are commutative, i.e. the order of queries is not important; it doesn't change the final result.
• The UNION ALL shows better performance as compared to UNION because resources are not wasted in filtering duplicates and sorting the result set.
• Set operators can be part of subqueries.
• In the case of MINUS operation, Oracle supports MINUS operation while PostgreSQL and SQL Server support EXCEPT for operator. MySQL does not perform MINUS/EXCEPT operation.

Conclusion

In this article, we discussed four different types of set operations in SQL- UNION, UNION ALL, INTERSECT, and MINUS. We used different examples for different databases with their syntaxes.

---

---