Union (SQL)

From Wikipedia, the free encyclopedia

In SQL the UNION operator combines the results of two SQL queries into a single table of all matching rows. The two queries must have matching fields and data types in order to join them. Any duplicate records are automatically removed unless UNION ALL is used.

UNION can be useful in data warehouse applications where tables aren't perfectly normalized.^[1] A simple example would be a database having tables sales2005 and sales2006 that have identical structures but are separated because of performance considerations. A UNION query could combine results from both tables.

Note that UNION does not guarantee the order of rows. Rows from the second operand may appear before, after, or mixed with rows from the first operand. In situations where a specific order is desired, ORDER BY must be used.

1 Examples
2 See also
3 References
4 External links

[edit] Examples

Given these two tables:

sales2005
person	amount
Joe	1000
Alex	2000
Bob	5000

sales2006
person	amount
Joe	2000
Alex	2000
Zach	35000

executing this statement:

SELECT * FROM sales2005
UNION
SELECT * FROM sales2006

yields this result set, though the order of the rows can vary because no ORDER BY clause was supplied:

person	amount
Joe	1000
Alex	2000
Bob	5000
Joe	2000
Zach	35000

Note that there are two rows for Joe because those rows are distinct across their columns. There is only one row for Alex because those rows are not distinct for both columns.

UNION ALL gives different results, because it will not eliminate duplicates. Executing this statement:

SELECT * FROM sales2005
UNION ALL
SELECT * FROM sales2006

would give these results, again allowing variance for the lack of an ORDER BY statement:

person	amount
Joe	1000
Joe	2000
Alex	2000
Alex	2000
Bob	5000
Zach	35000

The discussion of full outer joins also has an example that uses UNION.

[edit] See also

Union (set theory)

[edit] References

^ "a UNION ALL views technique for managing maintenance and performance in your large data warehouse environment ... This UNION ALL technique has saved many of my clients with issues related to time-sensitive database designs. These databases usually have an extremely volatile current timeframe, month, or day portion and the older data is rarely updated. Using different container DASD allocations, tablespaces, tables, and index definitions, the settings can be tuned for the specific performance considerations for these different volatility levels and update frequency situations." Terabyte Data Warehouse Table Design Choices - Part 2 (URL accessed on July 25, 2006)

[edit] External links

Topics in database management systems (DBMS) ( view • talk • edit )
Concepts Database • Database model • Relational database • Relational model • Relational algebra • Primary key, Foreign key, Surrogate key, Superkey, Candidate key • Database normalization • Referential integrity • Relational DBMS • Distributed DBMS • ACID
Objects Trigger • View • Table • Cursor • Log • Transaction • Index • Stored procedure • Partition	Topics in SQL Select • Insert • Update • Merge • Delete • Join • Union • Create • Drop
Implementations of database management systems
Types of implementations Relational • Flat file • Deductive • Dimensional • Hierarchical • Object oriented • Temporal • XML data stores	Components Query language • Query optimizer • Query plan • ODBC • JDBC
Database products
Apache Derby • Berkeley DB • Caché • DB2 • DBase • Filemaker Pro • Firebird • H2 • Helix • Informix • Ingres • InterBase • Microsoft Access • Microsoft SQL Server • MySQL • NonStop SQL • OpenLink Virtuoso • OpenOffice.org Base • Oracle • Oracle Rdb • Paradox • Perst • PostgreSQL • SQLite • Sybase IQ • Sybase • Teradata • Visual FoxPro Other: Object-oriented (comparison) • relational (comparison)