SQL Subquery

Subquery in SQL

Subquery is a nested SELECT statement in order to provide output to outer query for data filtering purpose.
    → All subqueries must be enclosed in parentheses.
    → Subquery can have multiple columns to match with outer main query.
    → Subqueries will always return unique list of values.

➠ Subquery can be used in following SQL statements
➠ Restrictions for Subquery

➠ Subqueries can be broadly classified into 2 categories:
Basic Subquery / Nested Subquery / Inner Query
    → A basic subquery or nested subquery is a subquery that is independent of outer query but provides data to outer query to restrict data of final main query.

Correlated subquery
    → A correlated subquery is a subquery that uses values from the outer query to restrict result of final main query.
    → A correlated subquery returns a utmost 1 value for each row of its correlated outer table set.
    → As this processing is done for each row to filter data therefore it can be inefficient.
    → Queries with EXISTS / NOT EXISTS clauses will generally have correlated subqueries.