SQL | Union Clause

The UNION clause in SQL is a powerful tool used to combine the results of two or more SELECT statements into a single result set. It ensures that duplicate rows are removed by default, providing only distinct values. To include duplicate values as well, the UNION ALL clause is used.

The UNION clause simplifies querying across multiple tables, allowing developers to merge data effectively. In this article, we will discuss the syntax, examples, and use cases of the UNION clause, along with explanations and outputs for better understanding.

Union Clause in SQL

The UNION clause combines data from multiple SELECT queries, ensuring that results are consolidated into a single output. Here are the key features of the UNION clause:

1. Removes Duplicate Rows: The UNION clause ensures that only distinct rows are returned in the result set.
2. Includes Duplicates with UNION ALL: The UNION ALL clause returns all rows, including duplicates.
3. Compatible Data: The columns in each SELECT query must have the same number, order, and data type.

Syntax:

• UNION

SELECT column_name(s) FROM table1  
UNION 
SELECT column_name(s) FROM table2;

• UNION ALL

SELECT column_name(s) FROM table1 
UNION ALL
SELECT column_name(s) FROM table2;

Examples of Union Clause in SQL

Consider we have two tables name Student and Student_Details. Suppose we want to do a union operation to find the common roll no from both tables. Let's first create a table with the name student and insert some random data similarly, we will create another table with the name Student_details keeping in mind that there will be at least one column common here we will take roll_no as a common column.

Table 1: Students

Table 2: Student_Details

Example 1: Using UNION to Fetch Distinct roll_no

This example demonstrates how to combine roll_no values from the Students and Student_Details tables using the UNION clause. It removes duplicate roll numbers and returns only distinct values in the result set.

Query:

SELECT ROLL_NO FROM Students UNION 
SELECT ROLL_NO FROM Student_Details; 

Output:

Explanation:

In this query, the UNION clause combines the roll_no column from both tables. It removes duplicates and displays only distinct values. As a result, the roll numbers 1, 2, and 3 (present in both tables) appear only once in the final output.

Example 2: Using UNION ALL to Include Duplicates

This example uses the UNION ALL clause to combine roll_no values from the Students and Student_Details tables. Unlike UNION, it includes all duplicate values in the result set, making it useful for analysing repeated records.

Query:

SELECT ROLL_NO FROM Students UNION ALL 
SELECT ROLL_NO FROM Student_Details; 

Output:

Explanation:

The UNION ALL clause combines the roll_no column from both tables, including duplicates. Here, the roll numbers 1, 2, and 3 appear twice because they exist in both tables. This query is useful when duplicate records are required for analysis.

Example 3: Using UNION ALL with Conditions

To fetch ROLL_NO, NAME from Student table WHERE ROLL_NO is greater than 3 and ROLL_NO, Branch from Student_Details table WHERE ROLL_NO is less than 3, including duplicate values and finally sorting the data by ROLL_NO.

Query:

SELECT ROLL_NO,NAME FROM Students WHERE ROLL_NO>3 
UNION ALL
SELECT ROLL_NO,Branch FROM Student_Details WHERE ROLL_NO<3
ORDER BY 1; 

Output:

Explanation:

In this example, the first query retrieves roll_no and name from the students table where the roll number is greater than 3. The second query retrieves roll_no and branch from the student_details table where the roll number is less than 3. The UNION ALL clause combines these results, and the ORDER BY clause sorts the data by roll_no.