Subqueries and Compound Queries

Subqueries engine in VALENTINA allows to use the results of one query as a compound part of another one.

The query contained in WHERE or HAVING statement of another clauses is called subquery. Subqueries allow in natural way to activate queries expressed through results of another ones.

Show the list of offices for which the sales plan amount exceeds summarized sales plan of the office’s employees

SELECT office
FROM offices
WHERE target > ( SELECT SUM( quota )
                 FROM salesreps
                 WHERE rep_office = office )

calculates the summarized sales plan amount of all employees who work in the particular office for every office. The main query compares office sales plan with this value and depending on comparison result either adds this office to the query results’ table or not. Jointly main and subqueries express source query and show the required information from database.

Subqueries widely used as part of WHERE or HAVING statement. In WHERE statement they help to express advanced search conditions ( often with external table dependency ) for each candidate-record selected in main query. In HAVING statement such conditions are checked for each group of records.

Subquery should be always in round brackets. Subqueries’ structure is identical to the ordinal query.

Differences between subquery and SELECT command:

• Subquery resulting table always consists of one field. It means that in SELECT statement of subquery should be single column only.
• ORDER BY statement can’t be the part of subquery. Subquery result is used only inside the main query and it’s invisible for the end-user, so there is no sense to sort them.
• Subquery may refer to the outer query columns.
• Subquery can’t be the join query; it is defined as only single-table SELECT command.

When subquery is contained in WHERE statement, it takes part in the record selection process. Usually subquery is a part of selection condition and returns the value which gives the possibility to check whether the condition is true or false.

In the example: Show the list of offices for which the sales plan amount exceeds summarized sales plan of the office’s employees

SELECT office
FROM offices
WHERE target > ( SELECT SUM( quota )
                 FROM salesreps
                 WHERE rep_office = office )

Subquery results can’t be computed once, as it returns different results for every office. The subquery giving the summarized plan of sales for employees of current office is executed for every row of main query result. Subquery result is a single number and subquery execution is repeated for each record which is checked by WHERE statement of main query.

Often in subquery body it is necessary to refer to field value in current record of main query.

“Outer reference” is the name of the subquery’s column which is not the part of any table, listed in FROM statement of subquery and which belongs to the table indicated in FROM statement of main query. The outer reference value is an outer query’s column value of the record which is checked in the present moment by main query.

• Comparison with subquery result. Expression value is comparing to the single value which is returning by subquery. This check is similar to the simple comparison.
• Check value to be in subquery results. Expression value is checking on equality to one of the set values returning by subquery. This check is similar to the ordinal check for value to be in the set.
• Existence check. The existence of the any records in subquery results’ table is checking.
• Multiple comparisons. Expression value is comparing to every one from the set values which are returning by subquery.

Expression value is comparing to the single value which is returning by subquery and if this condition is fulfilled, the check gives TRUE result.

Show the list of employees whose amount of sales is equal or more than whole sales amount of the office placed in Atlanta.

SELECT name
FROM salesreps
WHERE quota >= ( SELECT target
                 FROM offices
                 WHERE city = ‘Atlanta’ )

In comparison operation with subquery result you can use the same six comparison instructions ( =, <>, <, ⇐, >, >= ), as for ordinal value comparison.

Subquery taking part in such condition should return the single value as a result, e.g. one row containing one column. The comparison has no sense for multi-row or multi-column result. And DBMS produces error message in such a case.

If you get no value as a result of subquery execution the comparison operation will return NULL.

Subquery in comparison operation can be placed in any side of comparison instruction.

Single value is comparing to data-field returning by subquery and if this value is equal to one of the field elements the check gives TRUE result. This check is used when it is necessary to compare value from the checked record with the number of values selected by subquery

Show the list of employees of the offices where real sales amount exceeds planned value.

SELECT name
FROM salesreps
WHERE rep_office IN ( SELECT office 
                      FROM offices
                      WHERE sales > target )

Subquery returns the data-field as a result and WHERE statement of main query checks whether the value from the main query record is equal to one of the values got in that field.

Thus, IN-check with subquery is executed analogical to ordinal IN statement, with the exception that the set of values is obtained as result of subquery execution, rather than be directly wrote in the explicit form.

EXISTS predicate allows to clear up whether the subquery result contains any record. Here is no analogy with generic queries. Existence check is allowed only in subqueries. If subquery gets no one record EXISTS check returns FALSE value otherwise returns TRUE.

This check can’t return NULL.

It is possible to change EXISTS check logic using NOT EXISTS form. Then in case subquery gets no records the check returns TRUE, and FALSE in opposite case.

Note. EXISTS predicate in fact doesn’t use subquery results. You just check the existence of any results. So, in EXISTS check the usage of “SELECT *” form is allowed. In subquery with EXISTS check you always have outer reference “linking” the subquery with the checked main query record.

Show the list of offices where is an employee whose plan exceeds 55% over the office one.

SELECT city
FROM offices
WHERE EXISTS ( SELECT *
               FROM salesreps
               WHERE rep_office = office
               AND quota > ( .55 * target ))

VALENTINA has two variants of multiple comparisons - ANY and ALL, widening IN check to the level of other comparison instructions such as more (>) or less (<). In both checks some value is checking with data field got as result of subquery execution.

This check uses one of six comparison instructions (=, <>, <, ⇐, >, >= ). Checked value is compared with every element contained in subquery result. If any of these comparisons gets TRUE, ANY-check returns TRUE as result.

Show the list of employees who got the order on the sum exceeds 10% of their selling plan.

SELECT name
FROM salesreps
WHERE ( .1 * quota < ANY ( SELECT amount
                           FROM orders
                           WHERE rep = empl_num ))

Rules determining ANY check results, when checked value is comparing to subquery result.

• If subquery returns the empty result ANY-check returns FALSE value ( There is no any value for which condition is true ).
• If condition is TRUE for even one value in the subquery result ANY-check returns TRUE ( there is some value in subquery result for which comparison condition is fulfilled ).
• If condition is FALSE for all subquery’s result values ANY-check returns FALSE ( in other words that comparison condition is not fulfilled for any value returned by subquery ).
• If condition is NOT TRUE for all subquery’s result values, but there is one or several NULL values, ANY-check returns NULL value.

Show names and age data of all employees who don’t manage the office

SELECT name, age
FROM salesreps
WHERE NOT (empl_num = ANY(SELECT mgr FROM offices))

You can use EXISTS predicate instead of ANY wherever you want, just moving comparison operation inside the subquery’s selection condition.

The same as above example with EXISTS check usage:

SELECT name, age	
FROM salesreps
WHERE NOT EXISTS( SELECT *
                  FROM offices	
                  WHERE empl_num = mgr )

Instead of ANY predicate you can use SOME predicate, usually you can apply any of them.

In this check, one of the six comparison instructions (=, <>, <, ⇐, >, >= ) is used for the comparison of the checked value with the data selected by subquery.

Checked value is compared with every element contained in the subquery result. If all conditions are passed, ALL-check returns TRUE value.

Show list of offices where all employees’ percent of sales exceed 50% border of office sales plan.

SELECT city, target
FROM offices
WHERE ( .50 * target ) < ALL ( SELECT sales
                               FROM salesreps
                               WHERE rep_office = office )

Rules determining ALL-check result, checked value is comparing to subquery result.

• If subquery returns the empty result ALL-check returns TRUE value. It is considered that comparison condition is true even if there are no subquery results.
• If condition is TRUE for each value in the subquery result ALL-check returns TRUE value. Comparison condition is fulfilled for every value returned by subquery.
• If condition is FALSE for any subquery’s result value ALL-check returns FALSE value. (in other words that comparison condition is not fulfilled for every value returned by subquery.)
• If condition is NOT FALSE for anyone subquery’s result value, but gives NULL result for one or for several values ALL-check returns NULL value. In this case you can’t assert with the certain whether for all values returned by subquery the comparison condition is true or not; may be for all may be not - everything depends on “real” values of unknown data.

You can use EXISTS predicate instead of ALL wherever you want, just moving comparison operation inside the subquery’s selection condition.