Combining the results of multiple queries (UNION, EXCEPT, INTERSECT, MINUS, subqueries)

UNION

Combines the results of two or more queries into a single result set. This set includes all the rows that belong to all queries in the union.

The UNION operation is different from using joins that combine columns from two tables.

Limitations

• Each SELECT statement within UNION must have the same number of columns

• The columns must also have similar data types

• The columns in each SELECT statement must also be in the same order

UNION syntax

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

UNION ALL Syntax

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

EXCEPT/INTERSECT

EXCEPT returns distinct rows from the left input query that aren't output by the right input query.

INTERSECT returns distinct rows that are output by both the left and right input queries operator.

Syntax

{ <query_specification> | ( <query_expression> ) }
{ EXCEPT | INTERSECT }
{ <query_specification> | ( <query_expression> ) }

Examples

SELECT ProductID
FROM Production.Product
INTERSECT
SELECT ProductID
FROM Production.WorkOrder;
--Result: products that have work orders

SELECT ProductID
FROM Production.Product
EXCEPT
SELECT ProductID
FROM Production.WorkOrder;
--Result: products without work orders

MINUS

MINUS operator is used to return all rows in the first SELECT statement that are not returned by the second SELECT statement.

Each SELECT statement will define a dataset. The MINUS operator will retrieve all records from the first dataset and then remove from the results all records from the second dataset.

TIP: The MINUS operator is not supported in all SQL databases. It can used in databases such as Oracle.

For databases such as SQL Server, PostgreSQL, and SQLite, use the EXCEPT operator to perform this type of query.

Syntax

SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
MINUS
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];

Subqueries

A subquery is a query that is nested inside a SELECT, INSERT, UPDATE, or DELETE statement, or inside another subquery.

A subquery can be used anywhere an expression is allowed.

Restrictions

A subquery is subject to the following restrictions:

• The select list of a subquery introduced with a comparison operator can include only one expression or column name.

• If the WHERE clause of an outer query includes a column name, it must be join-compatible with the column in the subquery select list.

• The ntext, text, and image data types cannot be used in the select list of subqueries.

• The DISTINCT keyword cannot be used with subqueries that include GROUP BY.

• The COMPUTE and INTO clauses cannot be specified.

• ORDER BY can only be specified when TOP is also specified.

• A view created by using a subquery cannot be updated.

• The select list of a subquery introduced with EXISTS, by convention, has an asterisk (*) instead of a single column name.

Example

SELECT Ord.SalesOrderID, Ord.OrderDate,
    (SELECT MAX(OrdDet.UnitPrice)
     FROM Sales.SalesOrderDetail AS OrdDet
     WHERE Ord.SalesOrderID = OrdDet.SalesOrderID) AS MaxUnitPrice
FROM Sales.SalesOrderHeader AS Ord;