Subquery in SQL

A subquery in SQL is a query that is nested inside another query and used to retrieve the data necessary for the main query (it is also known as a nested query or inner query). Subqueries can be used in the WHERE clause, the FROM clause, or the SELECT statement in a SQL statement and are denoted by brackets.

Here's an example of a subquery used in the WHERE clause:

SELECT name, age, city FROM users WHERE city = (SELECT city FROM addresses WHERE zip_code = '123456');

The main query in this illustration returns the name, age, and city of people who reside in the same city as the zip code "123456." The city is retrieved from the addresses table using the subquery contained inside the WHERE clause when the zip code is '123456'. Prior to running the main query, the subquery is done, and the output is utilised to filter the data. To retrieve the data required to calculate a column, subqueries can also be utilised in the SELECT statement:

SELECT name, age, (SELECT AVG(score) FROM test_scores WHERE user_id = users.id) AS avg_score FROM users;

In this illustration, the main query returns the user's name, age, and average score while the subquery collects each user's average score from the test_scores table. To determine the average score for each user, the subquery is executed for each entry in the main query. Although powerful, subqueries can negatively impact a query's performance if they are not used correctly. Utilizing the right indexes and avoiding unneeded nested queries are crucial for optimizing subqueries.