From clause can be used to specify a sub-query expression in SQL. The relation produced by the sub-query is then used as a new relation on which the outer query is applied.
- Sub queries in the from clause are supported by most of the SQL implementations.
- The correlation variables from the relations in from clause cannot be used in the sub-queries in the from clause.
SELECT column1, column2 FROM (SELECT column_x as C1, column_y FROM table WHERE PREDICATE_X) as table2 WHERE PREDICATE;
Note: The sub-query in the from clause is evaluated first and then the results of evaluation are stored in a new temporary relation.
Next, 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 1: Find all professors whose salary is greater than the average budget of all the departments.
select I.ID, I.NAME, I.DEPARTMENT, I.SALARY from (select avg(BUDGET) as averageBudget from DEPARTMENT) as BUDGET, Instructor as I where I.SALARY > BUDGET.averageBudget;
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.
- Difference between Having clause and Group by clause
- SQL | Distinct Clause
- SQL | WHERE Clause
- SQL | SELECT TOP Clause
- SQL | Union Clause
- SQL | WITH clause
- SQL | Except Clause
- SQL | OFFSET-FETCH Clause
- SQL | LIMIT Clause
- SQL | Intersect & Except clause
- SQL | USING Clause
- SQL | With Ties Clause
- SQL | ON Clause
- Combining aggregate and non-aggregate values in SQL using Joins and Over clause
- SQL query using COUNT and HAVING clause
- Difference between Where and Having Clause in SQL
- Difference between order by and group by clause in SQL
- Difference between From and Where Clause in SQL
- Distinct clause in MS SQL Server
- Where clause in MS SQL Server
If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to email@example.com. See your article appearing on the GeeksforGeeks main page and help other Geeks.
Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.
Improved By : Dharmesh Singh 2