Subqueries

Ricardo Tovar — Fri, 03 Jan 2025 01:29:10 +0000

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');

DEV Community: Ricardo Tovar

Subqueries