Set operations (SQL)

Set operations (SQL)

=UNION operator=

In SQL the UNION clause combines the results of two SQL queries into a single table of all matching rows. The two queries must have the same number of columns and compatible data types to unite. 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. ["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." [http://www.dbazine.com/datawarehouse/dw-articles/beulke4 Terabyte Data Warehouse Table Design Choices - Part 2] (URL accessed on July 25, 2006)] 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.

Note that UNION ALL may be much faster than plain UNION.

Examples

Given these two tables:

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 sales2005UNION ALLSELECT * FROM sales2006;

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

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

INTERSECT operator

The SQL INTERSECT operator takes the results of two queries and returns only rows that appear in both result sets. For purposes of duplicate removal the INTERSECT operator does not distinguish between NULLs. The INTERSECT operator removes duplicate rows from the final result set. The INTERSECT ALL operator does not remove duplicate rows from the final result set.

Example

The following example INTERSECT query returns all rows from the Order table where Quantity is between 50 and 100.

SELECT *FROM OrderWHERE Quantity BETWEEN 1 AND 100

INTERSECT

SELECT *FROM OrderWHERE Quantity BETWEEN 50 AND 200;

EXCEPT operator

The SQL EXCEPT operator takes the distinct rows of one query and returns the rows that do not appear in a second result set. The EXCEPT ALL operator does not remove duplicates. For purposes of row elimination and duplicate removal, the EXCEPT operator does not distinguish between NULLs.

Notably, the Oracle platform provides a MINUS operator which is functionally equivalent to the EXCEPT DISTINCT operator [http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14200/ap_standard_sql003.htm#g14847] .

Example

The following example EXCEPT query returns all rows from the Order table where Quantity is between 1 and 49, and those with a Quantity between 76 and 100.

SELECT *FROM OrderWHERE Quantity BETWEEN 1 AND 100

EXCEPT

SELECT *FROM OrderWHERE Quantity BETWEEN 50 AND 75;

ee also

*Union (set theory)
*
*Select (SQL)

References

External links

* [http://msdn2.microsoft.com/en-us/library/ms180026.aspx MSDN documentation on UNION in Transact-SQL for SQL Server]
* [http://www.sqlexpert.co.uk/2007/11/order-by-clause-in-statements-with.html Naming of select list items in set operations]
* [http://www.mysqltutorial.org/sql-union-mysql.aspx UNION in MySQL with Examples]
* [http://dev.mysql.com/doc/refman/5.0/en/union.html UNION in MySQL]
* [http://www.postgresql.org/docs/current/static/sql-select.html#SQL-UNION UNION Clause in PostgreSQL]
* [http://www.w3schools.com/sql/sql_union.asp SQL UNION and UNION ALL]
* [http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.dwe.tutorial.doc/tutetlmod1_lesson2.htm Designing a data flow that loads a warehouse table]
* [http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/queries004.htm Oracle 11g documentation for UNION (ALL), INTERSECT and MINUS]
* [http://www.gplivna.eu/papers/sql_set_operators.htm SQL Set Operators]


Wikimedia Foundation. 2010.

Игры ⚽ Нужна курсовая?

Look at other dictionaries:

  • Set operation — may have one of the following meanings.*Any operation with sets *Set operations (Boolean), in a narrower sense, in the algebra of sets *Set operations (SQL) *Fuzzy set operations * Set operation …   Wikipedia

  • SQL-92 — was the third revision of the SQL database query language. Unlike SQL 89, it was a major revision of the standard. For all but a few minor incompatibilities, the SQL 89 standard is forwards compatible with SQL 92.New Features* SQL Agent * New… …   Wikipedia

  • Set-builder notation — In set theory and its applications to logic, mathematics, and computer science, set builder notation (sometimes simply set notation ) is a mathematical notation for describing a set by stating the properties that its members must satisfy. Forming …   Wikipedia

  • SQL — This article is about the database language. For the airport with IATA code SQL, see San Carlos Airport. SQL Paradigm(s) Multi paradigm Appeared in 1974 Designed by Donald D. Chamberlin Raymond F. Boyce Developer …   Wikipedia

  • Microsoft SQL Server — Developer(s) Microsoft Stable release SQL Server 2008 R2 (10.50.2500.0 Service Pack 1) / July 11, 2011; 4 months ago …   Wikipedia

  • Null (SQL) — The Greek lowercase omega (ω) character is used to represent Null in database theory. Null is a special marker used in Structured Query Language (SQL) to indicate that a data value does not exist in the database. Introduced by the creator of the… …   Wikipedia

  • Join (SQL) — An SQL join clause combines records from two or more tables in a database.[1] It creates a set that can be saved as a table or used as is. A JOIN is a means for combining fields from two tables by using values common to each. ANSI standard SQL… …   Wikipedia

  • Nested set model — The nested set model is a particular technique for representing nested sets (also known as trees or hierarchies) in relational databases. The term was apparently introduced by Joe Celko; others describe the same technique without naming it [1] or …   Wikipedia

  • System Center Operations Manager Command Shell — Windows PowerShell Screenshot von PowerShell 1.0 Basisdaten Entwickler: Microsoft Corporation Aktuelle Version …   Deutsch Wikipedia

  • Order by (SQL) — An ORDER BY clause in SQL specifies that a SQL SELECT statement returns a result set with the rows being sorted by the values of one or more columns. The sort criteria do not have to be included in the result set. The sort criteria can be… …   Wikipedia

Share the article and excerpts

Direct link
Do a right-click on the link above
and select “Copy Link”