Questions tagged [subquery]

A subquery is a query that is nested inside a SELECT, INSERT, UPDATE, or DELETE statement, or inside another subquery. A subquery can be used anywhere an expression is allowed. In this example a subquery is used as a column expression named MaxUnitPrice in a SELECT statement.

"Subquery" is vague SQL terminology, used to describe when there's:

• either more than one SELECT statement in a query, or when used in Data Manipulation Language (DML) statements (IE: DELETE, UPDATE, INSERT, MERGE).
• The subquery's SELECT statement is always found within brackets/parenthesis.

It's vague because most refer to any of the following as subqueries:

###Subselect The following can only return one value - more than one will cause an error.

SELECT f.column,
       (SELECT b.col
          FROM BAR b) AS col2
  FROM FOO f

Here's a correlated version of the subselect:

SELECT f.column,
       (SELECT b.col
          FROM BAR b
         WHERE b.col2 = f.col2) AS col2
  FROM FOO f

###Derived Table/Inline View

SELECT f.*
  FROM (SELECT t.*
          FROM FOOBAR t) AS f

###IN/NOT IN

SELECT f.*
  FROM FOO f
 WHERE f.column IN (SELECT b.col
                      FROM BAR b)


SELECT f.*
  FROM FOO f
 WHERE f.column NOT IN (SELECT b.col
                          FROM BAR b)

###EXISTS/NOT EXISTS Most would call the following a "correlated subquery", but only if they don't know how the EXISTS operator works in SQL.

SELECT f.*
  FROM FOO f
 WHERE EXISTS (SELECT NULL
                 FROM BAR b
                WHERE b.col = f.column)


SELECT f.*
  FROM FOO f
 WHERE NOT EXISTS (SELECT NULL
                     FROM BAR b
                    WHERE b.col = f.column)

Conclusion

Subqueries (and correlated subqueries) only return one value, and the database will return an error if the subquery will return more than one value.

Through better use of terminology, it becomes easier to communicate issues when problems are encountered.

Helpful articles

• How to write subqueries in SQL