Blog

Let’s dive into learning about the SQL UNION operator. SQL UNION is used to combine the results of two or more SELECT statements into a single result set. Each SELECT statement within the UNION must have the same number of columns, and the columns must have compatible data types. Here’s a detailed tutorial along with sample codes for different scenarios:

1. Understanding SQL UNION:

The UNION operator is used to combine the result sets of two or more SELECT statements into a single result set. The result set of a UNION query contains all the unique rows from the combined queries.

2. Syntax:

SELECT column1, column2, ...
FROM table1
WHERE condition1
UNION
SELECT column1, column2, ...
FROM table2
WHERE condition2;

3. Sample Scenario and Code:

Let’s consider two tables, employees and contractors, and we want to retrieve a list of all employees and contractors.

Step 1: Create Tables and Insert Sample Data

CREATE TABLE employees (
    emp_id INT,
    emp_name VARCHAR(50)
);

CREATE TABLE contractors (
    contractor_id INT,
    contractor_name VARCHAR(50)
);

INSERT INTO employees (emp_id, emp_name) VALUES
(1, 'John'),
(2, 'Alice'),
(3, 'Bob');

INSERT INTO contractors (contractor_id, contractor_name) VALUES
(101, 'Mike'),
(102, 'Sara');

Step 2: Using UNION to Combine Results

SELECT emp_id, emp_name FROM employees
UNION
SELECT contractor_id, contractor_name FROM contractors;

This will return a result set with the combined list of employees and contractors, where duplicate rows are eliminated.

4. Additional Scenarios:

Scenario 1: Retrieving All Unique Cities from Two Tables

Let’s say we have two tables, customers and suppliers, each containing a city column.

SELECT city FROM customers
UNION
SELECT city FROM suppliers;

Scenario 2: Combining Results with Ordering

(SELECT emp_id, emp_name FROM employees)
UNION
(SELECT contractor_id, contractor_name FROM contractors ORDER BY contractor_name);

This query will combine results from both tables and order them by contractor name.

Scenario 3: Filtering Results with WHERE Clause

(SELECT emp_id, emp_name FROM employees WHERE emp_id > 1)
UNION
(SELECT contractor_id, contractor_name FROM contractors WHERE contractor_id > 101);

This query combines results from both tables, but only includes rows where the ID is greater than 1 for employees and greater than 101 for contractors.

Conclusion

SQL UNION is a powerful tool for combining results from multiple SELECT statements. It allows you to merge data from different tables while ensuring uniqueness in the result set. By following the examples and understanding the syntax, you can effectively use UNION to manipulate and query data in SQL databases.