SQL UNION Operator

Understanding the SQL UNION Operator

Introduction:

The SQL UNION operator is used to combine the result sets of two or more SELECT statements into a single result set. It allows you to merge rows from different tables or queries based on compatible column data types. The UNION operator eliminates duplicates by default, making it an effective tool for data consolidation. In this article, we will explore the usage and benefits of the SQL UNION operator along with examples to help you understand its functionality effectively.

Syntax:

The general syntax for the SQL UNION operator is as follows:

        SELECT column1, column2, ...
        FROM table1
        UNION
        SELECT column1, column2, ...
        FROM table2;
    

Usage and Examples:

Let's consider a scenario where you have two tables: "employees" and "managers", both containing employee records with similar columns. You want to retrieve a combined list of all employees and managers. Using the SQL UNION operator, you can achieve this by merging the result sets of two SELECT statements.

        SELECT employee_id, first_name, last_name, job_title
        FROM employees
        UNION
        SELECT employee_id, first_name, last_name, job_title
        FROM managers;
    

The above query will retrieve a single result set that combines the employee records from both the "employees" and "managers" tables. The SELECT statements within the UNION operator should have the same number of columns and compatible data types. The UNION operator removes duplicate rows by default, ensuring that the final result set contains distinct records.

The SQL UNION operator can also be used with additional clauses like ORDER BY, WHERE, and GROUP BY to further refine the result set. For example, you can sort the merged result set based on a specific column:

        SELECT employee_id, first_name, last_name, job_title
        FROM employees
        UNION
        SELECT employee_id, first_name, last_name, job_title
        FROM managers
        ORDER BY last_name;
    

The above query will merge the employee and manager records, and then sort the result set by the "last_name" column in ascending order. By using the UNION operator along with other SQL clauses, you can manipulate the combined data as needed.

Conclusion:

The SQL UNION operator is a powerful tool for combining result sets from multiple SELECT statements. It allows you to consolidate data from different tables or queries into a single result set, eliminating duplicates by default. The UNION operator is widely used for data integration, report generation, and data analysis tasks. By understanding its syntax and usage, you can leverage the SQL UNION operator to efficiently merge and manipulate data in your database queries.