SQL Subquery Types of Subqueries

A subquery is a query within another query or an embedded query. Subqueries can be used in various ways in SQL queries to achieve complex queries and manipulate data. There are several types of subqueries available in SQL, each with its own syntax, example, output, explanation, and use.

Types of Subqueries

Scalar Subquery

A scalar subquery returns a single scalar value as output. The result of the subquery can be used as an operand in the parent query.

Syntax:

SELECT column_name 
FROM table_name 
WHERE column_name operator (select column_name from table_name where condition);

Example:

SELECT FirstName, LastName, (SELECT COUNT(*) FROM Orders WHERE Orders.CustomerID = Customers.CustomerID) AS OrderCount 
FROM Customers;

Output:

FirstName	LastName	OrderCount
Maria	Anders	5
Ana	Trujillo	8
Antonio	Moreno	13
...	...	...

Explanation:

In this example, a scalar subquery is used to count the number of orders for each customer in the parent query. The output of the scalar subquery is a single value, which is then used as an operand in the parent query.

Use:

Scalar subqueries can be useful in situations where a single value needs to be retrieved from a nested query and then used as an input value in an outer query.

Important Points:

• Scalar subqueries can only return a single value.
• Scalar subqueries can be nested inside other subqueries or SQL statements.

Correlated Subquery

A correlated subquery uses values from the outer query to perform a nested query. The subquery is executed for each row in the outer query, and the results are used in the parent query.

Syntax:

SELECT column_name 
FROM table_name a 
WHERE condition operator (SELECT column_name 
                          FROM table_name b 
                          WHERE a.column_name = b.column_name);

Example:

SELECT OrderID, ProductID, Quantity 
FROM OrderDetails A 
WHERE Quantity > (SELECT AVG(Quantity) 
                   FROM OrderDetails B 
                   WHERE A.ProductID = B.ProductID);

Output:

OrderID	ProductID	Quantity
10257	55	15
10280	44	12
...	...	...

Explanation:

In this example, a correlated subquery is used to retrieve the average quantity for each product. The subquery is correlated to the outer query on the ProductID column, and is executed for each row in the outer query. The output of the subquery is then used as a condition to filter the results of the outer query.

Use:

Correlated subqueries are useful in situations where the nested query depends on the values in the outer query.

Important Points:

• Correlated subqueries can be slower than other types of subqueries because they must be executed for each row in the outer query.
• The subquery in a correlated subquery must reference a column in the outer query.

Nested Subquery

A nested subquery is a subquery within a subquery. The inner query is executed first, and the results are used as input for the outer query.

Syntax:

SELECT column_name 
FROM table_name 
WHERE column_name operator (SELECT column_name 
                            FROM table_name 
                            WHERE condition);

Example:

SELECT CustomerID, ContactName, City 
FROM Customers 
WHERE Country IN (SELECT Country 
                  FROM Customers 
                  WHERE City = 'London');

Output:

CustomerID	ContactName	City
BLONP	Frédérique C	Strasbourg
BSBEV	Victoria A	London
...	...	...

Explanation:

In this example, a nested subquery is used to select all customers in cities where there are customers from London. The inner query retrieves the countries where there are customers from London and the outer query selects all customers in those countries.

Use:

Nested subqueries can be useful in situations where complex queries are required, and intermediate results need to be computed.

Important Points:

• Nested subqueries can be used to perform complex operations on the data.
• Nested subqueries can be executed in any order.

Summary

Subqueries are an essential feature of SQL and are used to create complex queries and manipulate data. There are several types of subqueries in SQL, including scalar, correlated, and nested subqueries. Each type of subquery has its own syntax, example, output, explanation, and use. By understanding the different types of subqueries available in SQL, you can create more effective and efficient queries.