Question

Can you give more cases when a subquery is needed and cannot be substituted with a join?

Answer 1

Hi,

A join returns a result table constructed from data from multiple tables. You can also retrieve the same result table using a subquery. A subquery is simply a SELECT statement within another select statement. This is a useful tool in building more complex and informative queries.

While SQL allows a subquery to return more than one column, most systems allow pairwise joining of one and only one column in an inner query with one column in an outer query (an exception to this is when a subquery is used with the EXISTS keyword). You can display data only from the table(s) referenced in the outer query. If you want to include data from the subquery in the result, then you would have to use the join technique because data from the subquery cannot be included in the results. The joining technique is useful when data from several relations are to be retrieved and displayed, and the relationships are not necessarily nested.

By using a subquery, the search can be carried out in just one query, instead of using one query to find the list of low-stock products and a second to find orders for those products.

The subquery makes a list of all values in the id column in the product table satisfying the WHERE clause search condition.

Remember the following notes about subqueries:

• Subqueries may also be useful in cases where you may have trouble constructing a join, such as queries that use the NOT EXISTS predicate.

• Subqueries can only return one column.

• Subqueries are allowed only as arguments of comparisons, IN, or EXISTS clauses.

• Subqueries cannot be used inside an outer join ON clause.