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
Return a single value.
Return multiple rows
It depends on a column in the main query, meaning it is executed once for each row in the outer query.
A subquery that contains another subquery within it.
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:
And each one of these parts have a set of rules .
For example :
SELECT
nombre,
(SELECT MAX(salary) FROM Employees) AS max_salary
FROM Employees;
SELECT NAME FROM EMPLOYEES WHERE DEPARTMENT_ID = (SELECT ID FROM DEPARTMENTS WHERE DEPARTMENT = 'IT');
More...
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');
More...