Advanced Techniques
Beyond the basic SELECT syntax, there are a few advanced techniques for
expressing more complex queries.
Subqueries
The SELECT
command provides a great deal of flexibility, but there are times
when a single SELECT command cannot
fully express a query. To help with these situations, SQL supports
subqueries. A subquery is nothing more
than a SELECT statement that is
embedded in another SELECT
statement. Subqueries are also known as sub-selects.
Subqueries are most commonly found in the FROM clause, where they act as a
computed source table. This type of subquery can return any number of
rows or columns, and is similar to creating a view or running the
query, recording the results into a temporary table, and then
referencing that table in the main query. The main advantage of using
an in-line subquery is that the query optimizer is able to merge the
subquery into the main SELECT
statement and look at the whole problem, often leading to a more
efficient query plan.
To use a subquery in the FROM clause, simply enclose it in parentheses. The following two statements will produce the same output:
SELECT * FROM TblA AS a JOIN TblB AS b; SELECT * FROM TblA AS a JOIN (SELECT * FROM TblB) AS b;
Subqueries can show up in other places, including
general expressions used in any SQL command. The
EXISTS and IN operators both utilize subqueries. In fact, you can use a subquery any place an expression expects a list of literal values (a subquery cannot be used to generate a list of ...