What is a Subquery?
Subqueries in SQL tables are used as inner database queries or existing table nested queries. A subquery in SQL tables is one that is embedded or inserted inside another SQL table query. Subqueries in SQL are used to perform those SQL table database operations for which the database engineer needs the result from another table query. This makes it easy and possible to filter, sort, collect data, transform data, store and retrieve a large volume of data in a more complex and dynamic order, in a table database. Subqueries in SQL tables can be used to handle multiple SQL database clause operations. You can retrieve or extract more than one database from a subquery in a SQL database table. For example, you can use clauses like SELECT, WHERE, FROM and HAVING etc.
Subquery in SQL table is first executed in the database. And the result of the subquery is used as an external query.
Types of Subqueries in SQL Database.
Subqueries in SQL table are mainly of two types.
- Scalar SQL table subquery
- Multi-row SQL table subquery
Furthermore, in SQL tables, table subqueries are divided into categories based on their different locations and usage.
- Subquery in SELECT clause.
- Subquery in WHERE clause.
- Subquery in FROM clause.
- Subquery in HAVING clause.
Scalar Subquery in SQL Table.
A scalar subquery in any SQL table returns the table result in a single value (single row and single column) value. Where this value can be used in database table expressions or comparisons.
Example of scalar subquery in SQL table.
Here we assume that we have two different SQL tables.
employe table with columns employee_id, name, salary, and department_id.
department table with columns department_id, department_name.
The first table is Employee table in which Employee_id, Employee_name, Employee_salary and Employee_department_id columns are table fields.
Similarly, the second is Department table in which Department_id, Department_name etc. are table columns.
Here in both the tables you want to retrieve the names of those employees whose salary is more than the average salary in their department.
SELECT name
FROM employe
WHERE salary > (
SELECT AVG(salary)
FROM employe
WHERE department_id = employe.department_id
);
Scalar Subquery in SQL Explanation.
The internal query (subquery) in the SQL table calculates the average salary for each department table.
The external query in the SQL table retrieves those employees whose salary is more than the average salary of the department.
Multi-row subquery in SQL Table.
A multi-row subquery in a SQL table returns multiple row values, and multi-row subqueries can be used with SQL database operators such as IN, ANY, and ALL to compare columns with multiple values returned by a subquery.
Example of a multi-row subquery in a SQL table.
Suppose in a SQL table you want to retrieve the names of employees who are working in ‘Mumbai’ or ‘Delhi’ location department. Here you have two different SQL tables.
The first table is the Employee table with columns like employee_id, employee_name, employee_department_id, etc.
The second table is the Department table with columns like department_id, department_name, department_location.
SELECT name
FROM employe
WHERE department_id IN (
SELECT department_id
FROM department
WHERE location IN (Mumbai, ‘Delhi’)
);
Explanation of a multi-row subquery in a SQL table.
Here the internal SQL table query returns the department_id value for departments located in ‘Mumbai’ or ‘Delhi’.
The same external SQL table query retrieves the names of employees working in those departments.
Subquery output in different SQL clauses in SQL tables.
Subquery in SELECT clause in SQL table.
Subquery is used in the SELECT clause to calculate the value for each row returned by the external query in a SQL table.
SELECT clause in SQL table Example.
SELECT name,
(SELECT AVG(salary) FROM employe WHERE department_id = e.department_id) AS avg_salary
FROM employe AS e;
SELECT clause Explanation.
Here the subquery in the SQL table calculates the average salary for each department, and returns it as an additional column avg_salary for each individual employee.
Subquery in WHERE clause in SQL table.
A common use of Subqueries in any SQL table is to filter the results based on a condition by combining the results of another query in the WHERE clause.
Subquery in WHERE clause Example.
SELECT name, salary
FROM employe
WHERE department_id = (
SELECT department_id
FROM department
WHERE department_name = ‘IT’
);
Subquery in WHERE clause Explanation.
Here the Subqueries in the SQL table retrieves department_id for the ‘IT’ department.
The outer query retrieves the employee information working in the ‘IT’ department by matching the department_id.
Subquery in the FROM clause in SQL table.
A table Subqueries in the FROM clause in SQL table is often referred to as an inline view or derived table. It creates a temporary table that is added or queried by the external query and displayed.
FROM clause in SQL table Example.
SELECT department_name, AVG(salary) AS avg_salary
FROM (
SELECT department_name, salary
FROM employe
JOIN department ON employe.department_id = departments.department_id
) AS dept_salaries
GROUP BY department_name;
FROM clause in SQL Explanation.
The Subqueries in the FROM clause in the table combines employees and departments, and provides a temporary result set (dept_salaries).
The external query uses this result set to calculate and display the average salary for each department.
Subquery in HAVING clause SQL table.
The Subqueries in the HAVING clause in SQL table is used to filter the results of collected data.
HAVING clause SQL table example.
SELECT department_name, AVG(salary) AS avg_salary
FROM employe
JOIN departments ON employe.department_id = departments.department_id
GROUP BY department_name
HAVING AVG(salary) > (
SELECT AVG(salary)
FROM employe
);
HAVING clause SQL table explanation.
The Subqueries in the HAVING clause in the existing SQL table calculates the total average salary of all employees.
The outer query retrieves the departments where the average salary is greater than the total average salary.
Advantages and Disadvantages of Subqueries in SQL Database Table.
Advantages of Subqueries.
- simplicity – Subqueries in SQL database tables help you break down complex database queries into smaller, manageable pieces of information.
- flexibility – Subqueries in SQL database tables are used with multiple elements (SELECT, WHERE, FROM, HAVING) of an SQL query.
- Readability – Subqueries in SQL database tables make the query more flexible by merging complex logical conditions.
Disadvantages of Subqueries.
- Performance – In some cases, Subqueries (especially co-related) can be less efficient than using joins because they can be the output of multiple executions of the Subqueries for individual rows of the outer query.
- Complexity – For complex Subqueries with multiple layers, understanding and debugging the query can be more complex.
Conclusion of Subquery in SQL Database Table.
- Subqueries in SQL Database Table are a powerful data and information extraction tool or feature that allows you to nest one query inside another.
- It is possible to break down complex queries using Subqueries in SQL Database Table.
- Subqueries in a SQL database table make it possible to perform operations where the results of one query are needed by another.
- Subqueries in a SQL database table make it easy to filter, aggregate, or compare data based on intermediate results.
