SQL subqueries (nested queries)

A subquery in SQL, also known as a nested query or inner query, is a query that is embedded within another SQL query. A subquery can be nested within the SELECT, INSERT, UPDATE, or DELETE statement or inside another subquery.

Subqueries can return individual values or a list of records; how many values the subquery returns depends on how it is used in the main query.

Subquery Syntax

A subquery is placed inside parentheses and typically introduced with a comparison operator.

SELECT column_name [, column_name ]
FROM   table1 [, table2 ]
WHERE  column_name OPERATOR
   (SELECT column_name [, column_name ]
   FROM table1 [, table2 ]
   [WHERE])

Example of a Subquery

Consider the following two tables:

Orders:

OrderID	CustomerID	Amount
1	3	100
2	1	230
3	2	150
4	4	200

Customers:

CustomerID	Name
1	Alfreds
2	Ana
3	Antonio
4	Thomas

Let's say you want to find all customers making orders of an amount higher than the average order amount. First, you would need to find the average order amount:

SELECT AVG(Amount) FROM Orders;

Assuming the average amount is 170.

Then, we can find all customers who have made orders greater than this average:

SELECT Name 
FROM Customers
WHERE CustomerID IN 
  (SELECT CustomerID 
  FROM Orders 
  WHERE Amount > 170);

The subquery (SELECT CustomerID FROM Orders WHERE Amount > 170) is run first and it returns a list of CustomerIDs. The outer query then uses this list to find the names of the customers from the Customers table.

Subqueries can be nested at various levels and can become quite complex, but they are very powerful tools for solving complex problems. As always, please refer to the documentation for the specific SQL implementation you are using, as there can be variations in the syntax and behavior.

Using Subqueries in WHERE Clause SQL:
- Description: Subqueries in the WHERE clause are used to filter the results based on the results of another query.
- Code Example:
```
SELECT column1
FROM table1
WHERE column2 = (SELECT column2 FROM table2 WHERE condition);
```
Correlated Subqueries in SQL:
- Description: Correlated subqueries reference columns from the outer query, providing a way to perform row-by-row comparisons.
- Code Example:
```
SELECT column1
FROM table1 t1
WHERE column2 > (SELECT AVG(column2) FROM table1 t2 WHERE t1.category = t2.category);
```
Scalar Subqueries in SQL:
- Description: Scalar subqueries return a single value and can be used in expressions.
- Code Example:
```
SELECT column1, (SELECT MAX(column2) FROM table2) AS max_value
FROM table1;
```
Subqueries in SELECT Statement SQL:
- Description: Subqueries in the SELECT statement are used to retrieve values to be used as part of the result set.
- Code Example:
```
SELECT column1, (SELECT AVG(column2) FROM table2) AS average_value
FROM table1;
```
Subqueries vs JOINs in SQL:
- Description: Both subqueries and JOINs can be used to retrieve related data, but subqueries are generally used for simpler, more focused tasks, while JOINs are suitable for complex data retrieval.
- Code Example (Subquery):
```
SELECT column1
FROM table1
WHERE column2 IN (SELECT column2 FROM table2 WHERE condition);
```
  Code Example (JOIN):
```
SELECT table1.column1
FROM table1
INNER JOIN table2 ON table1.column2 = table2.column2
WHERE table2.condition;
```
Subquery Optimization Techniques in SQL:
- Description: To optimize subqueries, consider using EXISTS, IN, or JOIN clauses based on the specific requirements and characteristics of the data.
- Optimization Example (Using EXISTS):
```
SELECT column1
FROM table1 t1
WHERE EXISTS (SELECT 1 FROM table2 t2 WHERE t1.column2 = t2.column2);
```
  Optimization Example (Using JOIN):
```
SELECT t1.column1
FROM table1 t1
INNER JOIN table2 t2 ON t1.column2 = t2.column2;
```