|
|
|
|
|
- Overview
- UNION/UNION ALL operators
- Overview
- UNION
- UNION ALL
- Union of queries with parameters: managing the parameters
- INTERSECT operator
- Overview
- INTERSECT
- Union of queries with parameters: managing the parameters
SQL set operations: UNION, UNION ALL and INTERSECT
SQL allows performing set operations on groups of files: UNION/UNION ALL operators Overview Combining queries are used to combine SELECT queries (SELECT statement). Each select query must be based on files of identical structure. To create a combining query, you have the ability to use: - the UNION operator.
- the UNION ALL operator.
Example: Create a query containing the customers whose name starts with the letter 'A' and the prospects who live in London. To create this query, the two following queries must be combined: - selecting the customers whose last name starts with the letter 'A'.
- selecting the prospects who live in London.
The records common to both files can: - appear once only in the result of the query (UNION operator).
- appear twice in the result of the query (UNION ALL operator).
UNION The UNION operator is used to combine some Select queries that apply to files of identical structure. The common records will be displayed only once in the query result. To create a combining query with the UNION operator, each SELECT query must use the same number of items. The caption of result items correspond to the caption of the items found in the first select query. Use format: SELECT Item1 [, Item2] FROM File1 [, File2] [WHERE ...] UNION SELECT Item1 [, Item2] FROM File1 [, File2] [WHERE ...] Example: The following SQL code is used to select the customers whose name starts with the letter 'A' and the prospects who live in London. The people who are both customer and prospect, whose name starts with the letter 'A' and who live in London will only appear once in the result of the query. SELECT CustomerName
FROM CUSTOMER
WHERE CustomerName LIKE 'A%'
UNION
SELECT ProspectName
FROM PROSPECT
WHERE CustomerCity = 'London' UNION ALL The UNION ALL operator is used to combine Select queries that apply to files of identical structure. The common records will be displayed several times in the query result. To create a combining query with the UNION ALL operator, each SELECT query must use the same number of items. The caption of result items correspond to the caption of the items found in the first select query. Use format: SELECT Item1 [, Item2] FROM File1 [, File2] [WHERE ...] UNION ALL SELECT Item1 [, Item2] FROM File1 [, File2] [WHERE ...] Example: The following SQL code is used to select the customers whose name starts with the letter 'A' and the prospects who live in London. The people who are both customer and prospect, whose name starts with the letter 'A' and who live in London will appear twice in the result of the query. SELECT CustomerName
FROM CUSTOMER
WHERE CustomerName LIKE 'A%'
UNION ALL
SELECT ProspectName
FROM PROSPECT
WHERE CustomerCity = 'London' Union of queries with parameters: managing the parameters When a combining query is created (UNION), each query has its own parameters. For example: - myQuery1.sparam1
- myQuery2.sparam2
- myUnionQuery is the UNION query of myQuery1 and myQuery2.
At runtime, the Union query and the queries used to build the Union query are distinct. In our example, "myQuery1.sparam1" initializes the parameter for the myQuery1 query but not the sParam1 parameter of the Union query. To get the right result, you must initialize myUnionQuery.sparam1 to initialize the parameter 1 of the Union query. Overview The INTERSECT operator can also be used to combine SELECT statements. Each select query must be based on files of identical structure. INTERSECT returns only common records. Example: Create a query containing the customers whose name starts with the letter 'A' and the prospects who live in London. To create this query, the two following queries must be combined: - selecting the customers whose last name starts with the letter 'A'.
- selecting the prospects who live in London.
Caution: - A specific management of parameters is necessary for intersections between queries with parameters.
- The INTERSECT operator takes precedence over the UNION/UNION ALL operator.
- INTERSECT queries are not available in the query editor.
- Reverse engineering is not available on INTERSECT queries.
- INTERSECT queries cannot be used with Native Connectors.
INTERSECT The INTERSECT operator is used to combine SELECT queries that apply to files of identical structure. The common records will be displayed only once in the query result. To create a combining query with the INTERSECT operator, each SELECT query must have the same number of items. The caption of result items correspond to the caption of the items found in the first select query. Use format: SELECT Item1 [, Item2] FROM File1 [, File2] [WHERE ...] INTERSECT SELECT Item1 [, Item2] FROM File1 [, File2] [WHERE ...] Example: The following SQL code is used to select the customers whose name starts with the letter 'A' and the prospects who live in London. The people who are both customer and prospect, whose name starts with the letter 'A' and who live in London will only appear once in the result of the query. SELECT CustomerName
FROM CUSTOMER
WHERE CustomerName LIKE 'A%'
INTERSECT
SELECT ProspectName
FROM PROSPECT
WHERE CustomerCity = 'London' Union of queries with parameters: managing the parameters When a combining query is created with INTERSECT, each query has its own parameters. For example: - myQuery1.sparam1
- myQuery2.sparam2
- myUnionQuery is the INTERSECT query of myQuery1 and myQuery2
At runtime, the INTERSECT query and the queries used to build the INTERSECT query are distinct. In our example, "myQuery1.sparam1" initializes the parameter for the myQuery1 query but not the sParam1 parameter of the INTERSECT query. To get the right result, you must initialize myUnionQuery.sparam1 to initialize the parameter 1 of the INTERSECT query.
This page is also available for…
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|