SQL Subqueries

There are many different ways to solve a SQL problem. As requests for database information become more complex, the need for added programming tools has become more important. One of the biggest enhancements to SQL has been the SQL subquery. As the name implies, this is a query inside a main query.

There are different names for a subquery: inner query, nested SELECT, sub-SELECT. The main thing to note is that a subquery can be embedded in different places inside the main SQL statement. For example, a subquery can be used as a:
--As a column expression
--As a filter inside a WHERE or HAVING clause
--As a datasource inside the FROM clause

The syntax is straightforward: enclose the subquery in parentheses and do not use a semicolon at the end of the subquery statement. The innermost query is processed first, then the result is used in the main query.

The SQL standard defines three types of subqueries:
Row subquery:
A subquery that returns more than one column and one row.
Table subquery:
A subquery that returns one or more columns and zero to one or more rows.
Scalar subquery:
A subquery that returns only one column and one row, or in other words a single distinct value.

Row subqueries are used in the WHERE clause of the main query. The SQL standard defines a Row Value Constructor for use in the WHERE, HAVING, or ON clause. However, not many vendors have implemented it.  An example of a Row Value Constructor used as a subquery would be something like this:

SELECT student_id, last_name
FROM students
WHERE (instructor_id, department_id) = (100, 150);

Here, the SELECT is on columns in students table where other columns have certain values.  Although the Row Value Constructor is not implemented, Oracle created a compound WHERE clause that works like this:

SELECT student_id, instructor_id, department_id
FROM students
WHERE (instructor_id, department_id) IN (SELECT instructor_id, department_id FROM students WHERE student_id  IN (20, 30));

Here, the inner subquery is structured with two clauses in the WHERE statement. 

A table subquery is used primarily in the FROM clause of the main query, or as an insert into an existing table. As this subquery returns many columns and rows, it is used in the FROM clause of an existing query like so:

SELECT S.student_id, S.last_name, S.score, D.score_avg
FROM students,
(SELECT instructor_id, avg(score) as "score_avg" FROM students GROUP BY department_id) D
WHERE S.department_id=D.department_id AND S.score >  D.score_avg;

A scalar subquery returns only one value, and it is used widely. You can use a scalar subquery as a substitute for a value expression. In addition, it can be used as a column subquery or in the WHERE clause of the main query. Here is an example:

SELECT student_id, last_name, score
FROM students
WHERE score > (SELECT avg(score) FROM students);

Here, the subquery in the WHERE clause selects the average score for all students. The result is the WHERE clause sets up a dynamic query as the average score for all students changes as additional tests are given.