Nested Queries in SQL

Structured Query Language (SQL) is a programming language. SQL is used to manage data stored in a relational database. SQL has the ability of nest queries. A nested query is a query within another query. Nested query allows for more complex and specific data retrieval. In this article, we will discuss nested queries in SQL, their syntax, and examples.

Nested Query

In SQL, a nested query involves a query that is placed within another query. Output of the inner query is used by the outer query. A nested query has two SELECT statements: one for the inner query and another for the outer query.

Syntax of Nested Queries

The basic syntax of a nested query involves placing one query inside of another query. Inner query or subquery is executed first and returns a set of values that are then used by the outer query. The syntax for a nested query is as follows:

SELECT column1, column2, ... 
FROM table1 
WHERE column1 IN ( SELECT column1 
   FROM table2 
   WHERE condition );

Types of Nested Queries in SQL

Subqueries can be either correlated or non-correlated

Non-correlated (or Independent) Nested Queries

Non-correlated (or Independent) Nested Queries : Non-correlated (or Independent) subqueries are executed independently of the outer query. Their results are passed to the outer query.

Correlated Nested Queries

Correlated subqueries are executed once for each row of the outer query. They use values from the outer query to return results.

Execution Order in Independent Nested Queries

In independent nested queries, the execution order is from the innermost query to the outer query. An outer query won't be executed until its inner query completes its execution. The outer query uses the result of the inner query.

Operators Used in Independent Nested Queries

IN Operator

This operator checks if a column value in the outer query's result is present in the inner query's result. The final result will have rows that satisfy the IN condition.

NOT IN Operator

This operator checks if a column value in the outer query's result is not present in the inner query's result. The final result will have rows that satisfy the NOT IN condition.

ALL Operator

This operator compares a value of the outer query's result with all the values of the inner query's result and returns the row if it matches all the values.

ANY Operator

This operator compares a value of the outer query's result with all the inner query's result values and returns the row if there is a match with any value.

Execution Order in Co-related Nested Queries

In correlated nested queries, the inner query uses values from the outer query, and the execution order is different from that of independent nested queries.

• First, the outer query selects the first row.

• Inner query uses the value of the selected row. It executes its query and returns a result set.

• Outer query uses the result set returned by the inner query. It determines whether the selected row should be included in the final output.

• Steps 2 and 3 are repeated for each row in the outer query's result set.

• This process can be resource-intensive. It may lead to performance issues if the query is not optimized properly.

Operators Used in Co-related Nested Queries

In co-related nested queries, the following operators can be used

EXISTS Operator

This operator checks whether a subquery returns any row. If it returns at least one row. EXISTS operator returns true, and the outer query continues to execute. If the subquery returns no row, the EXISTS operator returns false, and the outer query stops execution.

NOT EXISTS Operator

This operator checks whether a subquery returns no rows. If the subquery returns no row, the NOT EXISTS operator returns true, and the outer query continues to execute. If the subquery returns at least one row, the NOT EXISTS operator returns false, and the outer query stops execution.

ANY Operator

This operator compares a value of the outer query's result with one or more values returned by the inner query. If the comparison is true for any one of the values returned by the inner query, the row is included in the final result.

ALL Operator

This operator compares a value of the outer query's result with all the values returned by the inner query. Only if the comparison is true for all the values returned by the inner query, the row is included in the final result.

These operators are used to create co-related nested queries that depend on values from the outer query for execution.

Examples

Consider the following sample table to execute nested queries on these.

Table: employees table

emp_id	emp_name	dept_id
1	John	1
2	Mary	2
3	Bob	1
4	Alice	3
5	Tom	1

Table: departments table

dept_id	dept_name
1	Sales
2	Marketing
3	Finance

Table: sales table

sale_id	emp_id	sale_amt
1	1	1000
2	2	2000
3	3	3000
4	1	4000
5	5	5000
6	3	6000
7	2	7000

Example 1: Find the names of all employees in the Sales department.

Required query

SELECT emp_name 
FROM employees 
WHERE dept_id IN (SELECT dept_id 
   FROM departments 
   WHERE dept_name = 'Sales');

Output

emp_name
John
Bob
Tom

Example 2: Find the names of all employees who have made a sale

Required query

SELECT emp_name 
FROM employees 
WHERE EXISTS (SELECT emp_id 
   FROM sales 
   WHERE employees.emp_id = sales.emp_id);

Output

emp_name
John
Mary
Bob
Alice
Tom

This query selects all employees from the "employees" table where there exists a sale record in the "sales" table for that employee.

Example 3: Find the names of all employees who have made sales greater than $1000.

Required query

SELECT emp_name 
FROM employees 
WHERE emp_id = ALL (SELECT emp_id 
   FROM sales 
   WHERE sale_amt > 1000);

Output

emp_name
John
Mary
Bob
Alice
Tom

This query selects all employees from the "employees" table. With the condition that where their emp_id equals all the emp_ids in the "sales" table where the sale amount is greater than $1000. Since all employees have made a sale greater than $1000, all employee names are returned.