SQL | SUB Queries

In SQL, a subquery, sometimes referred to as a subselect or inner query, is a query embedded within the main query. A subquery can return one or more values depending on its type. Subqueries are often used to return a single value for an operation, such as comparison, or a set of values for operations like IN.

Types of Subqueries:

Scalar Subquery: Returns a single value (one row and one column).
Row Subquery: Returns a single row but with multiple columns.
Column Subquery: Returns a single column but with multiple rows.
Table Subquery: Returns multiple rows and multiple columns.

Uses of Subqueries:

In SELECT statement:

SELECT column1, (SELECT column2 FROM table2 WHERE table1.id = table2.id) AS column2_alias
FROM table1;

In FROM clause:

SELECT alias.column1, alias.column2
FROM (SELECT column1, column2 FROM table1 WHERE condition) AS alias;

In WHERE clause:
```
SELECT column1, column2
FROM table1
WHERE column1 = (SELECT column1 FROM table2 WHERE condition);
```
This is particularly useful for operations using IN, NOT IN, EXISTS, NOT EXISTS, ANY, and ALL.

Common Scenarios:

Finding records in one table that do not have a match in another table:

SELECT column1 FROM table1
WHERE column1 NOT IN (SELECT column1 FROM table2);

Comparing against aggregate values from another table:

SELECT column1, column2
FROM table1
WHERE column2 > (SELECT AVG(column2) FROM table2);

Using EXISTS for existence check:

SELECT column1 FROM table1
WHERE EXISTS (SELECT 1 FROM table2 WHERE table1.column1 = table2.column2);

Fetching the top N rows for each category (a Correlated Subquery scenario):

SELECT t1.category, t1.product, t1.price
FROM products t1
WHERE (
  SELECT COUNT(*) 
  FROM products t2 
  WHERE t1.category = t2.category AND t1.price <= t2.price
) <= 3;

This example fetches the top 3 products (lowest prices) for each category.

Notes:

Performance: Subqueries, especially those that are not correlated, can often be more performant as compared to joins in certain scenarios. However, correlated subqueries, where the inner query is dependent on the outer query's current row, can be inefficient due to repeated evaluations.
Depth: While you can nest subqueries many levels deep, it's essential to consider readability and performance. Excessively nested subqueries can be difficult to debug and maintain.
Alternative Solutions: Often, there are alternative methods to achieve the same result without using subqueries, such as using JOINs or leveraging window functions.

When using subqueries, always ensure that the query is logically correct, produces the desired result, and performs efficiently. If performance is a concern, evaluate the query execution plan to identify potential bottlenecks.

How to use subqueries in SQL:
- Subqueries are queries embedded within another query. They can be used in various clauses such as SELECT, FROM, and WHERE.
```
SELECT column1
FROM example_table
WHERE column1 = (SELECT another_column FROM another_table WHERE condition);
```
Scalar subqueries in SQL:
- Scalar subqueries return a single value and can be used in places where a single value is expected.
```
SELECT column1, (SELECT MAX(another_column) FROM another_table) AS max_value
FROM example_table;
```
Correlated subqueries in SQL:
- Correlated subqueries reference columns from the outer query, making them dependent on the outer query.
```
SELECT column1
FROM example_table e
WHERE column1 > (SELECT AVG(another_column) FROM another_table a WHERE a.id = e.id);
```

Subqueries in SELECT, FROM, WHERE clauses:

Subqueries can be used in the SELECT, FROM, and WHERE clauses for various purposes.

SELECT column1, (SELECT MAX(another_column) FROM another_table) AS max_value
FROM example_table
WHERE column1 = (SELECT MIN(yet_another_column) FROM yet_another_table);

Subqueries with EXISTS and NOT EXISTS in SQL:
- EXISTS and NOT EXISTS are used to check for the existence of rows based on a subquery.
```
SELECT column1
FROM example_table e
WHERE EXISTS (SELECT 1 FROM another_table a WHERE a.id = e.id);
```
Subqueries with IN and NOT IN operators:
- IN and NOT IN are used to compare a value with a set of values returned by a subquery.
```
SELECT column1
FROM example_table
WHERE column1 IN (SELECT another_column FROM another_table WHERE condition);
```
Subqueries with ANY and ALL operators:
- ANY and ALL are used to compare a value with a set of values returned by a subquery.
```
SELECT column1
FROM example_table
WHERE column1 > ANY (SELECT another_column FROM another_table WHERE condition);
```

Using subqueries with aggregate functions in SQL:

Subqueries can be used with aggregate functions to perform calculations on grouped data.

SELECT department, AVG(salary) AS avg_salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees WHERE department = 'HR')
GROUP BY department;

Nested subqueries in SQL:

Subqueries can be nested, allowing for more complex queries.

SELECT column1
FROM example_table
WHERE column1 IN (SELECT another_column FROM (SELECT * FROM nested_table) AS nested_subquery);

Subqueries vs. joins in SQL:

Subqueries and joins can achieve similar results, but the choice depends on the specific requirements and performance considerations.

-- Using subquery
SELECT column1
FROM example_table
WHERE column1 IN (SELECT another_column FROM another_table WHERE condition);

-- Using join
SELECT e.column1
FROM example_table e
INNER JOIN another_table a ON e.column1 = a.another_column;

Handling NULL values in subqueries:

Handling NULL values in subqueries may involve using COALESCE or IS NULL conditions.

SELECT column1
FROM example_table
WHERE column1 = COALESCE((SELECT another_column FROM another_table WHERE condition), 'DefaultValue');