Name: Table Subqueries
Rating: 4.3 (967 reviews)
Author: fatimaalam1234

Table Subqueries

Subqueries, sometimes referred to as inner queries or nested queries, are queries that are embedded within the clause of another SQL query. There are different types of SQL subqueries that are frequently used including Scalar, Row, Column, and Table subqueries.

Scalar Subqueries
A scalar subquery is a query that returns exactly one column with a single value.

Example:

    SELECT column_name [, column_name ]
    FROM   table1 [, table2 ]
    WHERE  column_name operator
           (SELECT column_name [, column_name ]
            FROM table_name 
            WHERE condition);

Row Subqueries

Row subqueries are used to return one or more rows to the outer SQL select query.

Example:

    SELECT column_name [, column_name ]
    FROM   table1 [, table2 ]
    WHERE  (column_name [, column_name ])
          IN (SELECT column_name [, column_name ]
              FROM table_name 
              WHERE condition);

Column Subqueries

Column Subqueries are used to return one or more columns to the outer SQL select query.

Example:

    SELECT column_name [, column_name ]
    FROM   table1 [, table2 ]
    WHERE  (SELECT column_name [, column_name ]
            FROM table_name 
            WHERE condition);

Table Subqueries

Table subqueries are used in the FROM clause and return a table that can be used as a table-reference in an SQL statement.
Example:

  SELECT column_name [, column_name ]
    FROM
        (SELECT column_name [, column_name ]
         FROM   table1 [, table2 ])
    WHERE  condition;