Search code examples
sqlsubquery

Different types of subqueries

I'm studying about subqueries but I'm with some doubts about this topic. There are some types of subqueries? I already saw some sites that divide subqueries into "single-row subqueries, multiple column subqueries, multiple column subqueries". Others that say that are these types of subqueries "subquery as a new column, selecting data from a subquery, subqueries using multiple tables". So, Im, a bit confused about what are the types of subqueries that exist and how each type works. Do you know what types of subqueries exist and the difference between them? Or do you have any resource about this topic? Thanks!

Solution

  • I wouldn't call them different types of subquery... a subquery is just any query within another query. This can be applied in all sorts of different ways.

    For example you might want a column that shows the (same) max range value for each row using a subquery which returns a single result:

    SELECT ID, value
,(SELECT MAX(value) from TBL) AS MAX_VALUE
FROM TBL

    Or you might JOIN to a subquery as if it were a table/ view eg:

    SELECT *
FROM A
JOIN (
      SELECT B.col1, C.col2
      FROM B
      JOIN C on B.ID = C.ID
      WHERE B.col3 = 2
     ) SUB on A.col1 = SUB.col1

    There are countless other ways to use a subquery - but essentially it's just any query to get your required result that sits within a larger query.

    In terms of your examples - any query can be used to return a single row, or single value, or multiple columns - and may use one table, or multiple tables or indeed no tables. A subquery is no different - it just forms part of another query.