A subquery is a SELECT statement that can be used in place of an unknown value (which is then known when the subquery is performed). In fact, the subqueries are an indirect way to relate multiple tables, since the subquery can be built on a table other than the one in the main query.
You can use a subquery where a condition can be used (i.e. WHERE clauses and HAVING). Subqueries can also be nested, i.e. a subquery can call a subquery which itself calls a subquery, etc.
1.1 The operator IN
Because the subquery can return multiple rows, its result equals a set of values. The
operator adapted to this situation is IN.
Example:
Which products belong to a taxable type?
SELECT product_code FROM product WHERE type IN (SELECT type_name FROM type WHERE taxable <> 0);Conditions of use
The compared columns must be of compatible data types. If you compare several columns, you have to put them in parentheses. It is possible to use the operator ‘=‘, but it is imperative that the subrequest produces only one row (one value).
1.2 Interleaved subqueries
An interleaved subquery is performed when a condition of the subquery refers to a row of the main SELECT table. This means, unlike a simple subrequest, that the interleaved subquery is evaluated again for each row of the main query.
To be able to refer to a row of the main query table, you may have to use the explicit name of the columns with which the condition is built.
Explicit name
The explicit name of a column is the name of the table combined with the name of the column (table.column). One point separates the two names.
If the same table is used in the subquery and the main query, then it may be necessary to use an alias for the tables.
Alias of a table replaces, for the use of a query, the name of a table. The alias of a table is defined in the same way as the alias of a column, i.e. the alias is separated from the column by a space or by the AS keyword.
Which are the outings of inventory whose quantity of pieces outgoing is greater than the average quantity of all the exits carried out on the account of the same project?
SELECT out_output FROM output S1 WHERE quantity> (SELECT AVG (quantity) FROM output
S2 WHERE S2project_code = S1.code_project);1.3 The EXISTS operator
With subqueries, you can also use an operator not to provide values, but to simply ensure that conditions are met. This EXISTS operator has the task to verify that there is at least one row that meets the requested conditions.
SELECT list of columns FROM table WHERE EXISTS (subquery);In this case, the main request is executed if the subquery returns at least one row.
Note that no column is specified in the WHERE clause since no comparison of values is made. All that is requested is that the subquery find at least one row that meets the conditions specified in the subquery.
The EXISTS operator is often more useful with an interleaved subquery where the existence condition is rechecked at each row of the main query.
What are the product types to which no inventory product belongs?
SELECT type_name FROM type WHERE NOT EXISTS (SELECT * FROM product WHERE product.type = type.type_name);Move to the next article: SQL union of several queries
