Multi-table Query Kinds - Firebird

The three methods of retrieving data from multiple tables are quite distinct from one another and, as a rule, perform different kinds of retrieval tasks. Because joining and subquerying both involve merging streams of data from rows in different tables, their roles overlap under some conditions. A correlated subquery, which can form a relational linkage with columns in the main table, can sometimes enable a set specification to produce the same output as a join, without the use of a join. Union queries, on the other hand, do not merge streams; rather, they “stack” rows. Their role never overlaps with those of joining queries or subqueries.

Joins, subqueries, and unions are not mutually exclusive, although union sets cannot be joined to one another or to other sets. Joined and union set specifications can include subqueries, and some subqueries can contain joins.

Joining

Joining is one of the most powerful features of a relational database because of its capacity to retrieve abstract, normalized data from storage and deliver denormalized sets to applications in context. In JOIN operations, two or more related tables are combined by linking related columns in each table. Through these linkages, a virtual table is generated that contains columns from all of the tables.

Join operations produce read-only sets that cannot be targeted for INSERT, UPDATE, or DELETE operations. Some application interface layers implement ways to make joined sets behave as if they were updatable.

Subqueries

A subquery is a SELECT statement that is embedded within another query. Embedded query in-line query, nested query, and sub-select are all synonyms for subquery. They are used under a variety of conditions for reading data from other tables into the enclosing query. The rules for subqueries vary according to purpose. The data they retrieve is always read-only.

UNION Querie

Union queries provide the ability to extract rows with matching formats from different sets into a unified set that applications can use as if it were a single, read-only table. The subsets retrieved from the tables do not need to be related to one another—they simply have to match one another structurally.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Firebird Topics