In SQL, a subquery is a query within a query. It allows you to nest one query within another, which can help you to retrieve complex data from a database. In this blog, we will discuss subqueries and their types in SQL.

What is a Subquery?

A subquery is a query that is embedded within another query. It is used to retrieve data from one or more tables and then pass the results to the outer query. Subqueries can be used with SELECT, INSERT, UPDATE, and DELETE statements in SQL.

Types of Subqueries

There are two main types of subqueries: correlated and non-correlated.

Non-correlated Subqueries

Non-correlated subqueries are subqueries that can be executed independently of the outer query. These subqueries are executed first, and their results are used in the outer query.

For example, consider the following query:

SELECT *
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

In this query, the subquery is executed first, and it returns the average salary of all employees. Then, the outer query is executed, which returns all employees whose salary is greater than the average salary.

Correlated Subqueries Correlated subqueries are subqueries that depend on the outer query for their results. These subqueries are executed once for each row of the outer query.

For example, consider the following query:

SELECT *
FROM employees e
WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id);

In this query, the subquery is executed once for each department in the employee's table. It returns the average salary of employees in the current department. Then, the outer query is executed, which returns all employees whose salary is greater than the average salary in their department.

Conclusion

Subqueries are a powerful tool in SQL that allows you to retrieve complex data from a database. Understanding the two types of subqueries, non-correlated and correlated, is essential for writing efficient and effective SQL queries. By using subqueries, you can retrieve data that would otherwise be difficult or impossible to obtain with a single query.