How do you use subqueries in SQL, and what are their benefits? — SQL & Databases Interview Question #16

In SQL, a subquery is a query that is embedded within another query. Subqueries are used to retrieve data that will be used as part of the main query. Subqueries can be used in a variety of ways to help refine and filter query results, and to perform calculations and aggregations on the data.

Here is an example of a subquery:

SELECT * FROM employees WHERE department_id IN 
(SELECT department_id FROM departments 
WHERE department_name = 'Sales');

In this example, the subquery is used to retrieve the department_id values for the ’Sales’ department from the departments table. The main query then uses the IN operator to select all the employees whose department_id is in the list of department_id values retrieved by the subquery.

Subqueries can be used in several ways, including:

Filtering results: Subqueries can be used to filter the results of a query based on a specific criterion. For example, you can use a subquery to find all customers who have made a purchase in the last 30 days.

Calculating values: Subqueries can be used to calculate values that are used in the main query. For example, you can use a subquery to calculate the average salary for all employees in a department.

Comparing values: Subqueries can be used to compare values in the main query with values in another table. For example, you can use a subquery to find all employees whose salary is higher than the average salary for their department.

The benefits of using subqueries in SQL include:

Flexibility: Subqueries provide a flexible way to retrieve data from multiple tables and perform complex calculations and aggregations.

Simplification: Subqueries can help simplify complex queries by breaking them down into smaller, more manageable pieces.

Performance: Subqueries can sometimes improve query performance by reducing the amount of data that needs to be processed.

Overall, subqueries are a powerful tool in SQL that can be used to retrieve and manipulate data in a variety of ways. By understanding how to use subqueries effectively, you can write more complex and efficient SQL queries that meet your business requirements.