What is the purpose of the UNION operator in SQL and how does it work?

Question

Aryan Kumar · Answer

The UNION operator in SQL is used to combine the result sets of two or more SELECT statements into a single result set. The purpose of the UNION operator is to merge rows from different tables or queries while removing duplicate rows from the final result set. Each SELECT statement within the UNION must have the same number of columns in the result sets, and the corresponding columns must have compatible data types.

Here's a basic syntax of the UNION operator:

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

Key points about the UNION operator:

Columns and Data Types:

The SELECT statements within a UNION must have the same number of columns, and the corresponding columns must have compatible data types.

Duplicates Removal:

The UNION operator removes duplicate rows from the result set. If you want to include duplicate rows, you can use the UNION ALL operator.

Ordering:

The order of rows in the final result set is not guaranteed to be in any specific order unless you use the ORDER BY clause at the end of the last SELECT statement.

NULL Handling:

The UNION operator treats NULL values in columns as equal. If a column in one SELECT statement has a NULL value and the corresponding column in another SELECT statement has a non-NULL value, they are considered duplicates and only one of them will appear in the result set.

Here's a simple example to illustrate the use of the UNION operator:

SELECT employee_id, employee_name FROM employees
WHERE department = 'HR'
UNION
SELECT employee_id, employee_name FROM employees
WHERE department = 'IT';

In this example, the UNION operator is used to combine the results of two SELECT statements. The result will include distinct employee records from both the HR and IT departments.

It's worth noting that the UNION operator is different from the JOIN operation, which is used to combine rows from two or more tables based on a related column between them. UNION, on the other hand, is used to combine rows from the result sets of two or more SELECT statements.

forum

What is the purpose of the UNION operator in SQL and how does it work?

Can you answer this question?

1 Answers