INTERSECT (SQL)

Returns results that are in both of the specified queries.

Synopsis

query1 INTERSECT query2

Description

An INTERSECT combines two or more queries into a single query and returns only data that is found in the result set of both individual queries. The result set does not contain any duplicates.

For an intersect to be possible between two queries, the number of columns specified by each must match. Specifying queries that return different numbers of columns results in a SQLCODE -9 error. You can specify a NULL column in one query to pair with a data column in another query to match the number of columns.

InterSystems SQL determines the result column data types by evaluating both queries and returning the data type with the highest precedence as follows: VARCHAR, DOUBLE, NUMERIC, BIGINT, INTEGER, SMALLINT, TINYINT. Other data types, such as DATE, are not assigned precedence; if you want to return a data type that is not assigned precedence, you must use an explicit CAST statement. See the Examples for more information.

Arguments

query

A query, usually a SELECT statement, that combines one or more SELECT statements.

Examples

The following example returns its column as data type TINYINT, even though the DATE data type has higher precedence in other contexts.

SELECT MyTinyIntField FROM Table1
   INTERSECT
SELECT MyDateField FROM Table2

The following example uses a CAST statement to return a data type that is not assigned precedence by the query.

SELECT CAST(MyTinyInt AS DATE) FROM Table1
   INTERSECT
SELECT MyDateField FROM Table2

The following example creates a result set that contains a row for every entry found in the first query that is also found in the second query. The result set contains only one row per pair of matching entries; if the two tables were to contain the exact same set of data, the result set would be the same length as one of the tables. The ORDER BY clause operates on the result and orders the result set by the Name:

SELECT Name,Address,NULL
FROM Sample.Employee
   INTERSECT
SELECT Name,Address,Age
FROM Sample.Person
ORDER BY Name