Skip to content
Related Articles

Related Articles

SQL | Sub queries in From Clause
  • Last Updated : 24 Aug, 2020
GeeksforGeeks - Summer Carnival Banner

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.

Syntax:

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.

Query



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

Instructor relation:

InstructorIDNameDepartmentSalary
44547SmithComputer Science95000
44541BillElectrical55000
47778SamHumanities44000
48147ErikMechanical80000
411547MelisaInformation Technology65000
48898JenaCivil50000

Department relation:

Department NameBudget
Computer Science100000
Electrical80000
Humanities50000
Mechanical40000
Information Technology90000
Civil60000

Query:

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;

Output

InstructorIDNameDepartmentSalary
44547SmithComputer Science95000
48147ErikMechanical80000

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.

Attention reader! Don’t stop learning now. Get hold of all the important DSA concepts with the DSA Self Paced Course at a student-friendly price and become industry ready.

My Personal Notes arrow_drop_up
Recommended Articles
Page :