Union Operation in DBMS: Definition, Example, and Usage
union operation combines the results of two or more SELECT queries into a single result set, removing duplicate rows. It is used to merge data from multiple tables or queries that have the same number of columns and compatible data types.How It Works
The union operation works by taking the output of two or more queries and stacking them together into one combined list. Imagine you have two lists of names from different groups, and you want to see all unique names from both groups without repeats. The union operation does exactly that for database rows.
It requires that each query returns the same number of columns, and the data types in each column must be compatible. The database then merges these rows and removes any duplicates, so each unique row appears only once in the final result.
Example
This example shows how to use the union operation to combine two simple queries selecting names from two different tables.
SELECT name FROM employees UNION SELECT name FROM customers;
When to Use
Use the union operation when you need to combine data from multiple tables or queries that share the same structure but contain different data. For example, if you have separate tables for employees and customers and want a list of all unique people involved with your company, union helps you merge those lists.
It is also useful in reporting and data analysis when consolidating results from different sources without duplicates is important.
Key Points
- Union combines results from multiple queries into one.
- It removes duplicate rows automatically.
- All queries must have the same number of columns with compatible data types.
- Useful for merging data from different tables or sources.