A
combining query (corresponding SQL statement:
UNION) is used to combine several select queries. Each select query must be based on data files of identical structure.
For example, select the customers whose last name starts with the letter 'A' and the prospects who live in Paris. To perform this selection, the two following queries must be combined:
- selecting the customers whose last name starts with the letter 'A'. This query is based on the "Customer" data file.
- selecting the prospects who live in London. This query is based on the "Prospect" data file.
The "Customer" and "Prospect" data files have the same structure in the analysis.
The records that are common to both data files ("Customer" and "Prospect") can:
- appear once only in the result of the query (UNION operator)
- appear twice in the result of the query (UNION ALL operator)
To combine queries, you must know:
- the name of the queries to combine.
- whether the common records must appear once or several times in the query result.
Remark: The items displayed in the result of the combining query correspond to the items of the first query run. Therefore, in our example, 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.
The query editor automatically generates the SQL code of this query.
To see the SQL code of a query:
- Open the context menu of the query graph.
- Select "SQL code".
You also have the ability to press the F2 key.
For example:
SELECT
CUSTOMER.CustomerName AS CustomerName
FROM
CUSTOMER
WHERE
CUSTOMER.CustomerName LIKE 'A%'
UNION
SELECT
PROSPECT.CustomerName AS CustomerName
FROM
PROSPECT
WHERE
PROSPECT.CITY = 'London'
In order for the people who are both customer and prospect, whose last name starts with the letter 'A' and who live in London to appear several times in the query result, the SQL code must be:
SELECT
CUSTOMER.CustomerName AS CustomerName
FROM
CUSTOMER
WHERE
CUSTOMER.CustomerName LIKE 'A%'
UNION ALL
SELECT
PROSPECT.CustomerName AS CustomerName
FROM
PROSPECT
WHERE
PROSPECT.CITY = 'London'