Database Queries to One Table (Part 3)Article contents:
This is an excerpt from the Mondial database that is widely used for practice exercises in this article.
|Table 12: Extract from the MONDIAL Database|
|France||F||Paris||Ile de France||547030||58317450||Europe||100|
|United States||USA||Washington DC||Distr. Columbia||9372610||266476278||America||100|
Subqueries with Nested SELECT Statements
n SQL, it is possible to formulate further SELECT statements within SELECT statements. These nested SELECT statements are called subqueries or subselects. They are often used to formulate filter conditions in the context of WHERE or HAVING.
The following example illustrates the use of subqueries using the Country table: a duplicate-free, sorted list is to be output with names and areas of all countries that are larger than the average area of all countries. The SQL statement used for this purpose is shown in picture
The subquery shown here is used to determine a dynamic comparison value from the current dataset. A general schema for subqueries in SELECT statements is shown in picture
Subqueries are used when the comparison criterion is dynamic and not known at the time the SQL query is created. In the above example, the comparison value was calculated on the basis of the current dataset of a table. Which comparison operators are possible is determined by the result type of the subquery. Subqueries with exactly one return value can be distinguished from subqueries with multiline returns.
Subqueries with Exactly One Return Value
Although the result set of a SELECT statement is always a table, it can be required that this table contain exactly one value. In this case the result set of the SELECT statement of the subquery will contain exactly one column that contains exactly one data record. Only if these conditions are fulfilled can the already-known comparison operators be used. As shown in the above example, these conditions are fulfilled by the use of aggregate functions. In the event that no aggregate function is used, it must be ensured that the conditions for the result set are still fulfilled. Otherwise, an error message will be issued when the SELECT statement is executed.
Subqueries with Multiline Returns
An example of subqueries with multiline returns is shown in the picture which queries the names and capitals of the countries in the Country table that are located on a continent for which more than six countries are stored in Country.
The result table of the subquery contains two records:
The comparison operator IN checks whether a particular value (specified to the left of IN) is contained in a set of values (specified to the right of IN). Further typical comparison operators for quantities are explained in Table
|Typical Comparison Operators for Quantities|
|Columncomparative operatorALL value set||The value from the named column is compared with all values of the value set by a comparison operator (e.g., <, >, >=, <=). ALL is evaluated to TRUE if the comparison for all records of the subquery evaluate to TRUE.||Population FROM Country WHERE (Population/Area) > ALL (SELECT Population/Area FROM Country WHEREContinent=ʼEuropeʼ)ORDER BY Name;Returns the countries whose population density is greater than that of all countries in Europe. Note: Uses division as a mathematical function of SQL.|
|Column comparative operatorANY value set||The value of the named column is compared with all values of the value set by a comparison operator (e.g., <, >, >=, <=). ANY is evaluated to TRUE if the comparison is evaluated to TRUE for at least one subquery record.||SELECT> Name, Area FROM Country WHERE Area >= ANY (SELECT SUM(Area) FROM Country GROUP BY Continent); Returns the countries whose area is greater than or equal to the total area of a continent.|
|EXISTS||Checks whether the result set contains at least one data record. Usually requires a link from the main query to the subquery in the WHERE part of the subquery.||SELECT Lastname, Firstname FROM Customer a WHERE EXISTS (SELECTDelivery AddressForCustomer FROM Address b WHEREb.Delivery AdressForCustomer =a.CustomerID)Returns names and first names of customers for whom a delivery address has been saved.|