Advanced SQL Tutorial | Subqueries

What You Will Learn

• How to use subqueries in the SELECT, FROM, and WHERE statements
• The differences between subqueries, GROUP BY, and PARTITION BY
• How to apply subqueries to solve real-world problems

Key Concepts

Subqueries are inner queries or nested queries that return data used in the main query as a condition to specify the data to be retrieved. They can be used almost anywhere, including in the SELECT, FROM, and WHERE statements. Subqueries can be used to calculate aggregate values, such as average salary, and can be used in place of window functions. However, subqueries can be slower than other methods, such as using temporary tables or common table expressions (CTEs). Subqueries can also be used in the WHERE statement to filter data based on conditions specified in the subquery.

Code Examples

SELECT employee_id, salary, (SELECT AVG(salary) FROM employee_salary) AS all_average_salary

This code snippet uses a subquery in the SELECT statement to calculate the average salary for all employees.

SELECT * FROM (SELECT employee_id, salary, AVG(salary) OVER () AS all_average_salary FROM employee_salary) AS subquery

This code snippet uses a subquery in the FROM statement to calculate the average salary for all employees and then selects all columns from the subquery.

SELECT employee_id, job_title, salary FROM employee_salary WHERE employee_id IN (SELECT employee_id FROM employee_demographics WHERE age > 30)

This code snippet uses a subquery in the WHERE statement to filter employees who are over the age of 30.

Lesson Summary

In this lesson, we learned how to use subqueries in SQL to solve real-world problems. We started by using a subquery in the SELECT statement to calculate the average salary for all employees. We then compared this method to using PARTITION BY and GROUP BY, and discussed the trade-offs between these methods. We also learned how to use subqueries in the FROM statement to create a temporary table that can be queried. Finally, we used a subquery in the WHERE statement to filter employees who are over the age of 30. Throughout the lesson, we saw examples of how subqueries can be used to solve complex problems, but also learned about the potential performance drawbacks of using subqueries. By the end of this lesson, you should have a solid understanding of how to use subqueries in SQL and when to apply them.

Practice Exercise

Write a SQL query that uses a subquery in the SELECT statement to calculate the maximum salary for each job title, and then use this subquery to filter employees who have a salary greater than the maximum salary for their job title.

What Is Next

In the next lesson, we will learn about common table expressions (CTEs) and how they can be used to simplify complex queries. We will also learn about the differences between CTEs and subqueries, and when to use each.