Subquery In SQL

Pradeep

Subquery in SQL

Subquery is a nested SELECT statement in order to provide output to outer query for data filtering purpose.
    → All subqueries must be enclosed in parentheses.
    → Subquery can have multiple columns to match with outer main query.
    → Subqueries will always return unique list of values.

➠ Subquery can be used in following SQL statements

SELECT Statements to filter required rows.
DELETE Statements to delete rows as returned by subquery output.
UPDATE Statements.
View Definitions to restrict data.
Table creation to restrict limited set of data in new table.
Subquery support qualifiers like ALL, ANY, SOME, LIKE, NOT LIKE for outer query.
Subquery can be objects of an IN, NOT IN, EXISTS and NOT EXISTS clause.

➠ Restrictions for Subquery

Subqueries can be nested up to a depth of 64(maximun) else it will fail with below error.


Teradata:
*** Failure 3995 Maximum number of subquery nesting levels has been exceeded.

Mysql:
ERROR 1473 (HY000): Too high level of nesting for select

LIMIT / TOP n option cannot be used in subquery else it will fail with below error.


Teradata:
*** Failure 6916 TOP N Syntax error: Top N option is not supported in subquery.

Mysql:
ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

Most of the databases does not support ORDER BY clause in subquery but few databases(like mysql) does support it.
```
Teradata:
*** Failure 3706 Syntax error: ORDER BY is not allowed in subqueries.
```

➠ Subqueries can be broadly classified into 2 categories:

Basic / Nested subquery
Correlated subquery

Basic Subquery / Nested Subquery / Inner Query
→ A basic subquery or nested subquery is a subquery that is independent of outer query but provides data to outer query to restrict data of final main query.

➩ Example 1: is a example of subquery.
```
SELECT * FROM table1 
WHERE id IN 
      ( SELECT 
           id 
        FROM 
           table2 
      );
```
Explanation:
- In the above example, subquery 'SELECT id from table2' will return all the 'ids' present in table2.
- These 'ids' from subquery will be used to filter data in table1 using IN clause.
- Above query will return only those records from table1 where 'id' value is also present in table2.
➩ Example 2:
```
SELECT * FROM table1 
WHERE (id,transaction_dt) NOT IN 
      ( SELECT 
           id,
           transaction_dt
        FROM 
           table2 
      );
```
Explanation:
- In the above example, subquery 'SELECT id,transaction_dt from table2' will return all the 'ids & transaction_dt' pairs present in table2.
- These 'ids & transaction_dt' pairs from subquery will be used to filter data in table1 using NOT IN clause.
- Above query will return only those records from table1 where 'id & transaction_dt' pair values are also present in table2.

Correlated subquery
    → A correlated subquery is a subquery that uses values from the outer query to restrict result of final main query.
    → A correlated subquery returns a utmost 1 value for each row of its correlated outer table set.
    → As this processing is done for each row to filter data therefore it can be inefficient.
    → Queries with EXISTS / NOT EXISTS clauses will generally have correlated subqueries.

➩ Example 1:
```
SELECT * FROM employee a
WHERE salary > 
      ( SELECT 
           AVG(salary)
        FROM 
           employee b
        WHERE a.dept_no = b.dept_no
      );
```
Explanation:
- In the above example, 'a.dept_no = b.dept_no' equality is setting correlation between subquery table and main query table.
- Above query will return only those employees from table1 where 'dept_no' value is also present in table2 and their salary is greater than average salary of their department.
➩ Example 2:
```
SELECT * FROM table1 
WHERE EXISTS 
      ( SELECT 
           1 
        FROM 
           table2 
        WHERE table1.id=table2.id
      );
```
Explanation:
- In the above example, 'table1.id=table2.id' equality is setting correlation between subquery table and main query table.
- Above query will return only those records from table1 where 'id' value is also present in table2.
➩ Example 3:
```
SELECT * FROM table1 
WHERE NOT EXISTS 
      ( SELECT 
           1 
        FROM 
           table2 
        WHERE table1.id=table2.id
      );
```
Explanation:
- In the above example, 'table1.id=table2.id' equality is setting correlation between subquery table and main query table.
- Above query will return only those records from table1 where 'id' value is not present in table2.