O'Reilly logo

Discovering SQL: A Hands-On Guide for Beginners by Alex Kriegel

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

SUMMARY

A query within a query is a powerful mechanism to retrieve additional information. The subquery can appear in any type of SQL statement, including SELECT, INSERT, UPDATE, and DELETE. It can be used in any part of SQL statement clauses, including WHERE, FROM, and SELECT list.

There are two types of subqueries: correlated and noncorrelated. The former refers to a syntax in which the subquery references the outer query, and the latter represents a subquery that is independent of the outer query. A number of operators can be used to evaluate results returned by the subquery to the outer query, such as IN, EXISTS, ANY, SOME, and ALL. Subqueries can be used with the GROUP BY… HAVING clause in conjunction with aggregate functions.

Often, a subquery can be replaced with an equivalent JOIN syntax, which might result in performance gain. There is no practical limit on the nesting level for the subqueries, either correlated or noncorrelated, although some RDBMSs do impose limits.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required