Union (SQL) - AbsoluteAstronomy.com

UNION operator

SQL

SQL is a programming language designed for managing data in relational database management systems ....

the UNION clause combines the results of two SQL queries into a single table

Table (database)

In relational databases and flat file databases, a table is a set of data elements that is organized using a model of vertical columns and horizontal rows. A table has a specified number of columns, but can have any number of rows...

of all matching rows

Row (database)

In the context of a relational database, a row—also called a record or tuple—represents a single, implicitly structured data item in a table. In simple terms, a database table can be thought of as consisting of rows and columns or fields...

. The two queries must result in the same number of columns

Column (database)

In the context of a relational database table, a column is a set of data values of a particular simple type, one for each row of the table. The columns provide the structure according to which the rows are composed....

and compatible data type

Data type

In computer programming, a data type is a classification identifying one of various types of data, such as floating-point, integer, or Boolean, that determines the possible values for that type; the operations that can be done on values of that type; the meaning of the data; and the way values of...

s in order to unite. Any duplicate records are automatically removed unless UNION ALL is used.

UNION can be useful in data warehouse

Data warehouse

In computing, a data warehouse is a database used for reporting and analysis. The data stored in the warehouse is uploaded from the operational systems. The data may pass through an operational data store for additional operations before it is used in the DW for reporting.A data warehouse...

applications where tables aren't perfectly normalized

Database normalization

In the design of a relational database management system , the process of organizing data to minimize redundancy is called normalization. The goal of database normalization is to decompose relations with anomalies in order to produce smaller, well-structured relations...

. 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:

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.

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

NULLsNull (SQL)
Null is a special marker used in Structured Query Language  to indicate that a data value does not exist in the database.  Introduced by the creator of the relational database model, E. F. Codd, SQL Null serves to fulfill the requirement that all true relational database management systems  support...

. 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 Orders table where Quantity is between 50 and 100.

SELECT *
FROM Orders
WHERE Quantity BETWEEN 1 AND 100

INTERSECT

SELECT *
FROM Orders
WHERE 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 (not supported in MSSQL) does not remove duplicates. For purposes of row elimination and duplicate removal, the EXCEPT operator does not distinguish between

NULLsNull (SQL)
Null is a special marker used in Structured Query Language  to indicate that a data value does not exist in the database.  Introduced by the creator of the relational database model, E. F. Codd, SQL Null serves to fulfill the requirement that all true relational database management systems  support...

.

Notably, the Oracle platform provides a MINUS operator which is functionally equivalent to the SQL standard

SQL:2003

SQL:2003 is the fifth revision of the SQL database query language. The latest revision of the standard is SQL:2008.-Summary:The SQL:2003 standard makes minor modifications to all parts of SQL:1999 , and officially introduces a few new features such as:* XML-related features * Window functions* 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 Orders table where Quantity is between 1 and 49, and those with a Quantity between 76 and 100.

Worded another way; the query returns all rows where the Quantity is between 1 and 100, apart from rows where the quantity is between 50 and 75.

SELECT *
FROM Orders
WHERE Quantity BETWEEN 1 AND 100

EXCEPT

SELECT *
FROM Orders
WHERE Quantity BETWEEN 50 AND 75;

Alternatively, in implementations of the SQL language without the EXCEPT operator, the equivalent form of a LEFT JOIN where the right hand values are

NULLNull (SQL)
Null is a special marker used in Structured Query Language  to indicate that a data value does not exist in the database.  Introduced by the creator of the relational database model, E. F. Codd, SQL Null serves to fulfill the requirement that all true relational database management systems  support...

can be used instead.

Example

The following example is equivalent to the above example but without using the EXCEPT operator.

SELECT o1.*
FROM (
SELECT *
FROM Orders
WHERE Quantity BETWEEN 1 AND 100) o1
LEFT JOIN (
SELECT *
FROM Orders
WHERE Quantity BETWEEN 50 AND 75) o2
ON o1.id = o2.id
WHERE o2.id IS NULL

External links

The source of this article is wikipedia, the free encyclopedia. The text of this article is licensed under the GFDL.