What is a subquery, Nested Subqueries&Correlated Subqueries?

What is a subquery?

A subquery is a SELECT statement with another SQL statement, like in the example below:

select *
from employee
where id in
           (select employee_id
            from employee
            where id = 15);

Subqueries are further classified as either a correlated sub query or a nested sub query

Nested Subqueries
Nested sub-queries are sub queries that don't rely on an outer query. In other words, both queries in a nested sub query may be run as separate queries.
This type of sub query could be used almost everywhere



Select *
from employee
where city in (select city
               from employer);

The example subquery returns all clients that are from the same city as the product providers.
The IN operator checks if the value is within the table and retrieves the matching rows.

Correlated Subqueries
Subqueries are correlated when the inner and outer queries are interdependent, that is, when the outer query is a query that contains a subquery and the subquery itself is an inner query. Users that know programming concepts may compare it to a nested loop structure.

Let's start with a simple example. The inner query calculates the average value and returns it. In the outer query’s where clause, we filter only those purchases which have a value greater than the inner query’s returned value.

select id
from employee E1
where joiningdate > '2016-01-15'
and salary > (select avg(salary)
             from employee E2
             where E1.date = E2.date)