<< SQL dialect | SQL Language Reference | Symbols and brackets used in code syntax >>

Query

A query is a qualified search for information held in the data sets stored in the database. The qualification can determine which tables should be searched, which range of values for specified columns should be included, etc.

For an overview of the conditions that are available in SQL, please refer to Comparison Operators.

SUM (total), MIN (minimum), MAX (maximum), AVG (average), and COUNT are aggregates that can also be used, for example, when the sales department needs to know how many orders are still open or the minimum/maximum or average order value in the past year.

A query on one or more tables produces a set of rows that is itself a table, subject to all the rules for tables in a relational database. This is known as Closure. Firebird/InterBase^® fully supports closure.

Regularly performed queries, such as a list of all unpaid invoices, or a list of all delivery notes that have gone out in the last week, can be stored as procedures.

Queries are optimized by Firebird/InterBase^®. The optimizer chooses which indices should be used, in order to perform the query as quickly and simply as possible.

Subquery

A subquery is a second query within a main query. For example, a list of all products costing above the average price is required. This information can be attained by first ascertaining the average price of all products:

 select avg(price)
   from product

and then asking for a list of all products where the price is higher than this figure:

 select *
   from product
   where price > 25.40

It is quicker and easier is to use a subquery:

 select *
   from product
   where price > (select avg(price)
     from product)

back to top of page
<< SQL dialect | SQL Language Reference | Symbols and brackets used in code syntax >>