SQL | Subqueries in From Clause

SQL Subqueries are valuable features that enable us to embed one query within another, adding more importance to retrieving and manipulating data in complicated manner. It is used to set up temporary tables or intermediate results to be subsequently used by the outer query.

Subquery in the FROM Clause

FROM clause can be used to specify a subquery expression in SQL. The relation produced by the subquery is then used as a new relation on which the outer query is applied for further operations. Subqueries in the FROM clause is used when we need to create a temporary dataset that does not exist in any physical table. This can be extremely helpful when working with complex datasets.

The correlation variables from the tables referenced in the outer query's FROM clause cannot be used directly within the subqueries in the FROM clause. Given below are two syntax of using Subquery in the FROM clause used in different scenarios.

1. Simple Subquery in the FROM Clause:

SELECT column1, column2

FROM (SELECT column_x AS C1, column_y FROM table WHERE condition) AS subquery_table

WHERE outer_condition;

2. Subquery in the FROM Clause with JOIN:

SELECT column1, column2

FROM (SELECT column_x AS C1, column_y FROM table WHERE PREDICATE_X) AS table2

JOIN table1 ON table2.some_column = table1.some_column

WHERE PREDICATE;

Parameters:

• Subquery: The inner query enclosed in parentheses. It retrieves data that will act as a temporary table.
• Alias: The subquery must be given an alias (subquery_table) to be referenced in the outer query.
• Outer Query: The outer query can then work with this temporary table created by the subquery, applying filters or performing further operations.

How Subqueries in the FROM Clause Work

• Subquery Evaluation: The sub-query in the from clause is evaluated first and then the results of evaluation are stored in a new temporary relation. 
  
• Outer Query: After the subquery executes and returns a dataset, the outer query is evaluated, selecting only those tuples from the temporary relation that satisfies the predicate in the where clause of the outer query.

Example of Using Subquery in the FROM Clause

Lets discuss some real world examples to understand how Subqueries in the FROM Clause work in practice. Here we have two tables Instructor table that contains information about instructors, including their salary and department. Other one is Department table that contains information about different departments, including the department's budget.

Instructor Table 

Department Table

Example 1: Find all professors whose salary is greater than the average budget of all the departments. 

In this example we will use a subquery in the FROM clause to calculate the average budget of all departments and then do the comparison with the salaries of instructors.

Query:

SELECT I.InstructorID, I.Name, I.Department, I.Salary
FROM (SELECT AVG(Budget) AS averageBudget FROM Department) AS BUDGET,
Instructor AS I
WHERE I.Salary > BUDGET.averageBudget;

Output

Explanation:

• The average budget of all departments from the department relation is 70000.
• Erik and Smith are the only instructors in the instructor relation whose salary is more than 70000 and therefore are present in the output relation. 

Why Use Subqueries in the FROM Clause?

There are various advantages of using Subqueries in the FROM clause as given below:

• Simplification: They can simplify complex queries by breaking them down into smaller, more manageable parts. You don’t need to create temporary tables explicitly in your database.
• Improved Readability: Using subqueries in the FROM clause makes the query more readable since it divides data-aggregation logic from the core question.
• Flexibility: Subqueries enable you to carry out operations that otherwise would be inconvenient, like aggregations or filtering with respect to other datasets, without the need to create intermediate tables.

Conclusion

Subqueries in the FROM clause enables us to define temporary result sets that may be utilized by outer queries. They make complex SQL operations easier, enhance query readability, and provide flexibility in data filtering and aggregation. Knowing how to utilize subqueries within the FROM clause is a basic skill for every SQL practitioner, particularly when dealing with complex databases or when you have to carry out complex data analysis.