SUB QUERIES (NESTED QUERIES)

You can nest the SQL queries within one another. 

Nested query or sub-query is an SQL SELECT  statement inside another SELECT or action statement. 

SQL first evaluates the inner query. The result of the inner query is returned to the outer query.  

The results of this inner SELECT statement (or sub select) are used in the outer statement to help determine the contents of the final result. A sub-select can be used in the WHERE AND HAVING  clauses of an outer SELECT statement, where it is called a subquery or nested query. Sub selects may also appear in INSERT, UPDATE, and DELETE statements.

There are three types of subquery: 

A scalar subquery returns a single column and a single row, that is, a single value. In principle, a scalar subquery can be used whenever a single value is needed.

A row subquery returns multiple columns, but only a single row. A row subquery can be used whenever a row value constructor is needed, typically in predicates.

A table subquery returns one or more columns and multiple rows. A table subquery can be used whenever a table is needed, for example, as an operand for the IN predicate.