A subquery is a SELECT statement that can be used against an unknown value (which is then known when the subquery is performed). In fact, subqueries are an indirect way to relate multiple tables, since the subquery can be built on a table other than the main query.
A subquery can be used where a condition can be used (i.e. the WHERE and HAVING clauses).
Subqueries can also nest, i.e. a subquery can use a subquery that itself uses a subquery, and so on.
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.
Which products belong to taxable classes?
SELECT product_code FROM product WHERE class IN (SELECT class FROM class 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 produce only one line (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.
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 classes to which no inventory product belongs?
SELECT class FROM class WHERE NOT EXISTS (SELECT * FROM product WHERE product.class = class.class);