Developers can select rows from a query. For example, suppose they have already written a query retrieving patents and identifiers of the coinventorred patents — the patents with more than one inventor. The SQL might look like this:
SELECT
patents.patent_id,
description,
area
FROM
patentinventor
INNER JOIN
inventors
ON patentinventor.patent_id
= inventors.patent_id
GROUP BY
patents.patent_id,
description,
area
HAVING COUNT(*) > 1
Developers can then write another query that builds on this result. For example, they can write a query that retrieves the coinventorred avionic patents. To write this new query, they can use the existing query as the source of the new query’s data. The resulting SQL might look like this:
SELECT
description
FROM
(
SELECT
patents.patent_id,
description,
area
FROM
patentinventor
INNER JOIN
inventors
ON patentinventor.patent_id
= inventors.patent_id
GROUP BY
patents.patent_id,
description,
area
HAVING COUNT(*) > 1
)
co_inventorred_patents
WHERE area = ‘avionic’
Similarly, a query can participate in a JOIN operation. For example, developers can find the opened coinventorred patents merely by joining the OpenedPatents view to the query retrieving the coinventorred patents. The resulting SQL might look like this:
SELECT
OpenedPatents.description
FROM
OpenedPatents
INNER JOIN
(
SELECT
patents.patent_id,
description,
area
FROM
patentinventor
INNER JOIN
inventors
ON patentinventor.patent_id
= inventors.patent_id
GROUP BY
patents.patent_id,
description,
area
HAVING COUNT(*) > 1
)