A sub query is a query within a query. A subquery can take place in a SELECT, FROM, and WHERE clause. A subquery can be used in a INSERT, UPDATE, SELECT, and DELETE query.
The main query is called the outer query and the subquery in called the inner query.
The sub query is executed and its results are passed to the outer query which is then executed after.
SELECT column_name1, column_name2,.... FROM table_name1 WHERE column_name1 operator (SELECT column_name FROM table_name2 );
Here we are filter out rows with sales_rep_id that is less than the three smallest sales_rep_id which is, in this case, 3, 4, and 5.
Subquery in FROM clause
Subquery in the FROM clause forms a derived or temporary table. This derived or temporary table must have an alias.
As you can see, “(SELECT phone, credit_limit FROM customer) as tempCustomer” formed a derived table.
Correlated subquery
A correlated subquery is different from a regular subquery that stands alone. A correlated subquery uses columns or data from the outer query. It evaluates once for every row in the outer query.
Here is another example where we select all customers with address id that is greater than the average id of the employee.