Subqueries

Subqueries are queries that come within another larger query. They are useful to obtain specific information that will later be used in the main query.

Type

• Single row subquery:

Return a single value.

• Multirow subquery

Return multiple rows

• Correlated subquery

It depends on a column in the main query, meaning it is executed once for each row in the outer query.

• Nested subquery

A subquery that contains another subquery within it.

• Scalar subquery

Returns a single value(similar to single row subquery) but can be used as a column in the SELECT.

A subquery can appear in 3 places of your query:

• SELECT
• FROM OR INNER
• WHERE

And each one of these parts have a set of rules .
For example :

• A subquery in SELECT should return a single value like:

SELECT 
    nombre,
    (SELECT MAX(salary) FROM Employees) AS max_salary
FROM Employees;

• A subquery in FROM OR INNER should return a set of rows

• A subquery in WHERE should return a column with many rows like:
  

SELECT NAME FROM EMPLOYEES WHERE DEPARTMENT_ID = (SELECT ID FROM DEPARTMENTS WHERE DEPARTMENT = 'IT');