5. Subqueries

What are Subqueries?

Subqueries, also known as inner queries or nested queries, are SQL queries embedded within another SQL query. They are used to perform operations that depend on the results of another query. Subqueries can be used in SELECT, FROM, WHERE, and other SQL clauses.

Syntax

SELECT column1, column2
FROM table
WHERE column3 = (SELECT column_name FROM another_table WHERE condition);

Types of Subqueries

Single-Row Subqueries

Returns a single value, often used with comparison operators like =, >, <, >=, <=.

Example:

SELECT Name, Age
FROM Students
WHERE Age = (SELECT MAX(Age) FROM Students);

In this example, the subquery returns the maximum age, and the outer query retrieves the students with that age.

Multi-Row Subqueries

Returns multiple rows, often used with operators like IN, ANY, or ALL.

Example:

SELECT Name, Age
FROM Students
WHERE Age IN (SELECT Age FROM Students WHERE Age > 20);

This retrieves students whose ages are greater than 20, as determined by the subquery.

Correlated Subqueries

A correlated subquery depends on the outer query for its values. It is executed repeatedly, once for each row processed by the outer query.

Example:

SELECT Name, Age
FROM Students s1
WHERE Age > (SELECT AVG(Age) FROM Students s2 WHERE s1.ClassID = s2.ClassID)

This query retrieves students whose ages are above the average age for their class.

Scalar Subqueries

Returns a single value and is used directly in expressions.

Example:

SELECT Name, (SELECT COUNT(*) FROM Courses WHERE Courses.StudentID = Students.StudentID) AS CourseCount
FROM Students;

This query counts the number of courses each student is enrolled in and includes it as a column in the result set.

Subqueries in SQL Clauses

Subqueries in SELECT

Subqueries can be used to calculate derived values in the SELECT clause.

Example:

SELECT Name, (SELECT AVG(Age) FROM Students) AS AverageAge
FROM Students

Subqueries in WHERE

Subqueries in the WHERE clause filter rows based on the results of another query.

Example:

SELECT Name
FROM Students
WHERE Age > (SELECT AVG(Age) FROM Students);

Subqueries in FROM

Subqueries in the FROM clause are treated as temporary tables.

Example:

SELECT AVG(Age)
FROM (SELECT Age FROM Students WHERE ClassID = 1) AS Class1Ages;

Common Use Cases for Subqueries

Filtering Data

Use subqueries to filter data dynamically based on another table.

Example:

SELECT Name
FROM Students
WHERE ClassID = (SELECT ClassID FROM Classes WHERE ClassName = 'Math');

Joining Data

Use subqueries to simulate joins when direct joins are not practical.

Example:

SELECT Name
FROM Students
WHERE StudentID IN (SELECT StudentID FROM Enrollments WHERE CourseID = 101);

Aggregating Data

Use subqueries to calculate aggregate values for specific groups.

Example:

SELECT Name, Age
FROM Students
WHERE Age > (SELECT AVG(Age) FROM Students WHERE ClassID = 2);

Advantages of Subqueries

• Simplify complex queries by breaking them into smaller, manageable parts.

• Reduce the need for temporary tables in certain scenarios.

• Increase query readability when used appropriately.

Limitations of Subqueries

• Subqueries can sometimes be less efficient than joins, especially in correlated subqueries.

• Some database systems impose restrictions on the nesting depth of subqueries.

• Complex subqueries can reduce query readability and maintainability.

Best Practices

• Use subqueries only when they improve readability or simplify logic.

• For better performance, consider replacing correlated subqueries with joins where possible.

• Avoid deeply nested subqueries to maintain query clarity.

Reference

The content in this document is based on the original notes provided in Azerbaijani. For further details, you can refer to the original document using the following link:

Original Note - Azerbaijani Version