DBMS | Nested Queries in SQL

In a relational database management system (DBMS), nested queries, often referred to as subqueries, are SQL queries that have another SQL query embedded within them. A subquery can return a single value, a single row, a single column, or a table.

Types of Subqueries:

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

Examples:

Scalar Subquery: Get the average salary from the employees table.
```
SELECT AVG(salary) FROM employees;
```
Use the above subquery to find employees earning more than the average salary.
```
SELECT name, salary FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);
```

Row Subquery: Find employees with the same job_id and department_id as a specific employee (e.g., with employee_id = 100).

SELECT name FROM employees WHERE (job_id, department_id) = (SELECT job_id, department_id FROM employees WHERE employee_id = 100);

Column Subquery: Get a list of departments that have employees.

SELECT DISTINCT department_id FROM employees WHERE department_id IN (SELECT department_id FROM departments);

Table Subquery: Find the employees who have the maximum salary in their respective departments.

SELECT e.name, e.salary, e.department_id FROM employees e
WHERE (e.salary, e.department_id) IN 
(SELECT MAX(salary), department_id FROM employees GROUP BY department_id);

Nested Subqueries:

Subqueries can also be nested within other subqueries. For instance, to find the department name for employees who earn the maximum salary:

SELECT e.name, d.department_name FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE e.salary IN 
   (SELECT MAX(salary) FROM employees WHERE department_id IN 
      (SELECT department_id FROM departments));

Usage of Subqueries:

In SELECT clause: Subqueries can be used in the SELECT clause to return a single value.
In WHERE clause: As demonstrated in the examples above, subqueries can filter rows based on the result of the subquery.
In FROM clause: Subqueries can be used as derived tables in the FROM clause.

Points to Remember:

Order of Execution: Subqueries in the WHERE clause are typically executed before the main query.
Correlated Subquery: Sometimes, subqueries can reference columns from the outer query. Such subqueries are called correlated subqueries, and they can be more resource-intensive than regular subqueries.
Performance: Nested queries can sometimes lead to performance issues. It's essential to profile the SQL and ensure that it runs efficiently, especially on large datasets. Depending on the DBMS, it might be more performant to use JOINs rather than subqueries in certain scenarios.

In summary, nested queries or subqueries are a powerful feature of SQL, allowing for complex operations and data retrieval. However, they should be used judiciously, keeping performance implications in mind.

Examples of nested queries in database management:
- Nested queries involve embedding one query within another. For instance, retrieving data from a subquery result.
```
SELECT column1
FROM table1
WHERE column2 IN (SELECT column3 FROM table2 WHERE condition);
```
Nested subqueries in SQL explained:
- Nested subqueries are queries placed inside another query. They are enclosed within parentheses and can appear in various clauses.
```
SELECT column1
FROM table1
WHERE column2 = (SELECT column3 FROM table2 WHERE condition);
```
How to use nested queries in WHERE clause:
- Utilize nested queries in the WHERE clause for more complex conditions.
```
SELECT column1
FROM table1
WHERE column2 = (SELECT MAX(column3) FROM table2);
```
Correlated subqueries in database management systems:
- Correlated subqueries reference columns from the outer query, making them dependent on the outer query's context.
```
SELECT column1
FROM table1 t1
WHERE column2 = (SELECT MAX(column3) FROM table2 t2 WHERE t2.foreign_key = t1.primary_key);
```

Nested queries vs JOIN operations in SQL:

While nested queries and JOIN operations can achieve similar results, choosing between them depends on the specific use case and performance considerations.

-- Nested Query Example
SELECT column1
FROM table1
WHERE column2 = (SELECT MAX(column3) FROM table2);

-- JOIN Example
SELECT table1.column1
FROM table1
JOIN table2 ON table1.column2 = table2.column3
WHERE table2.column3 = (SELECT MAX(column3) FROM table2);

Nested queries in SELECT and FROM clauses:

Nested queries can also appear in the SELECT and FROM clauses, performing operations on the result set.

-- Nested Query in SELECT
SELECT column1, (SELECT MAX(column2) FROM table2) AS MaxValue
FROM table1;

-- Nested Query in FROM
SELECT AVG(subquery_column) AS AverageValue
FROM (SELECT column1 AS subquery_column FROM table1) AS subquery_table;

Common mistakes when using nested queries in SQL:
- Common mistakes include using incorrect syntax, not considering performance implications, and neglecting to handle cases where the subquery returns multiple rows.
```
-- Incorrect Example (subquery returns multiple rows)
SELECT column1
FROM table1
WHERE column2 = (SELECT column3 FROM table2 WHERE condition);
```