Subqueries in FROM Clauses
1234567891011121314151617SELECT d.name AS department_name, avg_salaries.avg_salary FROM departments d JOIN ( SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id ) AS avg_salaries ON d.department_id = avg_salaries.department_id ORDER BY avg_salaries.avg_salary DESC;
When you use a subquery in the FROM clause, you are creating a derived table that exists only for the duration of the query. In the example above, the subquery calculates the average salary for each department from the employees table. This result set is given an alias, avg_salaries, and is treated just like a regular table in the main query. The main query then joins this derived table with the departments table, allowing you to display each department's name alongside its average salary. This approach is especially useful in HR analytics, where you often need to combine aggregated results with other tables to produce meaningful reports. Using subqueries in the FROM clause helps you break down complex problems into manageable steps, making your SQL more readable and modular.
1. Which of the following is a key benefit of using a subquery in the FROM clause?
2. Fill in the blanks to complete the query that finds the average salary per department and joins it with the departments table:
Thanks for your feedback!
Ask AI
Ask AI
Ask anything or try one of the suggested questions to begin our chat
Can you explain what a derived table is in more detail?
How does the subquery improve the readability of the SQL?
Can you give another example of using a subquery in the FROM clause?
Awesome!
Completion rate improved to 5.56Subqueries in FROM Clauses
Swipe to show menu
1234567891011121314151617SELECT d.name AS department_name, avg_salaries.avg_salary FROM departments d JOIN ( SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id ) AS avg_salaries ON d.department_id = avg_salaries.department_id ORDER BY avg_salaries.avg_salary DESC;
When you use a subquery in the FROM clause, you are creating a derived table that exists only for the duration of the query. In the example above, the subquery calculates the average salary for each department from the employees table. This result set is given an alias, avg_salaries, and is treated just like a regular table in the main query. The main query then joins this derived table with the departments table, allowing you to display each department's name alongside its average salary. This approach is especially useful in HR analytics, where you often need to combine aggregated results with other tables to produce meaningful reports. Using subqueries in the FROM clause helps you break down complex problems into manageable steps, making your SQL more readable and modular.
1. Which of the following is a key benefit of using a subquery in the FROM clause?
2. Fill in the blanks to complete the query that finds the average salary per department and joins it with the departments table:
Thanks for your feedback!
