Using Subqueries in WHERE, FROM, and SELECT Clauses
In SQL database management systems, subqueries are a universal or versatile database insert or embed component that helps database users embed another existing database table query within an existing database query. Where the data is stored in the existing database system, database users can perform multiple individual subquery tasks or actions based on these database subqueries. Here, subqueries in database tables can be used in the WHERE, FROM, and SELECT clause expressions in table queries.
So, let’s understand the WHERE, FROM, and SELECT clause expressions in SQL database tables better.
Subqueries in the WHERE clause in SQL databases.
Subqueries in the WHERE clause in SQL database tables are generally used by subquery users to custom filter table data based on user-defined logic or conditions. These can help database users compare a table value to an existing list of values or find table rows that meet particular special conditions based on other database table data.
Syntax of the WHERE clause in a subquery.
SELECT column_name
FROM table_name
WHERE column_name operator (subquery);
Elements of a subquery.
- In SQL database management systems, logical operators can be used to compare logical expressions or conditions, such as =, >, <, >=, <=, <>, or the condition can be one of the logical set operators IN, EXISTS, ANY.
Example of using IN with a subquery in a database table.
Here, the database user wants to find the employees in the employee table who work in a specific or special department, such as the “Development” or “Design” department. Here, the database user can apply this by using a subquery in the WHERE clause.
SELECT emp_name
FROM employee
WHERE department_id IN (
SELECT department_id
FROM department
WHERE department_name IN (‘Development’, ‘Design’)
);
Explanation of the WHERE clause in a subquery.
- Here, the subquery in the employee table (SELECT department_id FROM department WHERE department_name IN (‘Development’, ‘Design’)) finds and returns the department_id values for the “Development” and “Design” departments.
- The external query then filters the list of employees who belong to any of the departments specified here.
Example of EXISTS with a subquery in a database table.
Here, the database user wants to find the employee categories that work in departments where at least one employee exists.
SELECT emp_name
FROM employee e
WHERE EXISTS (
SELECT 1
FROM employee
WHERE department_id = e.department_id
);
Explanation of EXISTS with a subquery.
- Here, the subquery in the employee database table checks if at least one employee exists in the same department e.department_id.
- The outer query returns the names of employees in departments where at least one employee exists.
FROM clause subquery in SQL database.
In SQL database management systems, subqueries that generate database tables in the FROM clause are mostly known as inline views or derived tables. The FROM clause in a database table is used to develop a temporary result set that the outer query can later treat as a table.
Syntax of a FROM clause subquery.
SELECT column_name
Elements of the FROM clause.
- Here, in the condition FROM (subquery) AS alias_name, the database table subquery is executed first, and its result is treated as a table in the outer query.
Example of a FROM clause subquery in an SQL database.
Here, let’s assume the database user wants to calculate the total salary for each department. So here, the database user can add the salaries by department by applying a subquery in the FROM clause, and then calculate the average salary of all departments.
SELECT department_id, AVG(total_salary) AS avg_department_salary
FROM (
SELECT department_id, SUM(salary) AS all_salary
FROM employee
GROUP BY department_id
) AS department_salary
GROUP BY department_id;
Explanation of FROM clause subqueries in SQL databases.
- Here, the table subquery calculates the total salary SUM(salary) for each department and displays the result grouped by department_id.
- Here, the outer table query calculates and displays the average total salary AVG(total_salary) for each department from the result of the subquery.
Use case of FROM clause subqueries in SQL databases.
Subqueries in the FROM clause of SQL database tables are essential when the database user needs to create a temporary table or view to simplify complex queries, or when the database user needs to perform aggregation or transformation on data that needs to be referenced in the outer database table query.
Subqueries in the SELECT clause of SQL database tables.
Subqueries in the SELECT clause of SQL database tables are mostly used to return a value for each row of the outer table query. This is done to calculate or compute the result of the table subquery for each table row, and the output result is returned and displayed as a separate column.
Syntax of subqueries in the SELECT clause.
SELECT column_name, (subquery) AS alias_name
FROM table_name;
Here, in this condition, the database table subquery executes once for each row of the outer query, and the result of this table subquery is displayed in the final output.
Example of a scalar subquery in SELECT.
Let’s assume the database user wants to display the name of each employee and the average salary of their department.
SELECT emp_name, (
SELECT AVG(salary)
FROM employee
WHERE department_id = e.department_id
) AS avg_department_salary
FROM employee e;
Explanation of the subquery in the SELECT clause.
- Here, the database table subquery SELECT AVG(salary) FROM employee WHERE department_id = e.department_id calculates the average salary for the employee’s department.
- The outer query retrieves and displays the employee’s name and includes the average salary of the related department for each employee.
Example of COUNT() in the SELECT clause.
Here, the database user wants to calculate the number of employees in each department.
SELECT department_id, (
SELECT COUNT(*)
FROM employee e2
WHERE e2.department_id = e1.department_id
) AS employee_count
FROM department e1;
Explanation of COUNT() in the SELECT clause.
- Here, the database table subquery SELECT COUNT(*) FROM employee e2 WHERE e2.department_id = e1.department_id counts the number of employees in each department.
- Here, the outer query retrieves all departments and their employee numbers.
Conclusion of Using Subqueries in WHERE, FROM, and SELECT Clauses.
- Subqueries in SQL database management systems are a powerful database table query extraction method or tool that helps database users create more dynamic and flexible queries. By analyzing and understanding how and where to use database queries in different clauses or expressions (WHERE, FROM, SELECT), database users can easily solve or control complex data retrieval issues or problems.
- Subqueries in the WHERE clause – This is used to filter database table data based on particular WHERE conditions defined by the subquery in the existing database. The WHERE clause is mostly used with comparison operators like IN, EXISTS, and ANY.
- Subqueries in the FROM clause – This creates a view of a temporary table in the existing database table. The FROM clause can use the table query as an outer query. This is useful for aggregating or transforming table data before querying it.
- Subqueries in the SELECT clause – This is useful for calculating database table values based on each table row in the existing database table. It can also be used to return calculated column values based on conditions in the database table subquery.
