SQL Subquery

SQL Subquery or SQL Inner Query or SQL Nested Query all mean the same thing and what it means is that there is a query with in a query.

SQL Subquery can be used with the following SQL statements:

SELECT, INSERT, UPDATE and DELETE.

Also, SQL Subqueries can be executed:

  • As a column in the main SQL query
  • As a filter in the WHERE condition of the main SQL query or in other SQL Subqueries
  • With the HAVING Clause in the main SQL Query or in other SQL Subqueries
  • As a data-source in the main SQL Statement in the FROM clause

Mostly, SQL Subquery is used when we know how to search for a data but do not know the exact value for data. Well, this will become clear in the following examples of SQL subqueries.


Examples of SQL Subquery are:

For all the examples below let’s assume that we have a table named ‘employee’ and ‘department’ in the database as shown below respectively:

Employee_ID Employee_Name Salary Department_ID Commission
101 Emp A 10000 1000
102 Emp B 20000 2000 20
103 Emp C 28000 2000 20
104 Emp D 30000 3000  5
105 Emp E 32000 1000 10
106 Emp F 32000 1000 5
108 Emp H 12000 3000
Department_ID Department_Name
1000 Sales
2000 IT
3000 Support

Example 1: Using SQL Subquery in WHERE Clause of SQL SELECT Statement

Suppose we wish to view the details of employees working in the ‘Sales’ department, but we don’t know the ‘department_id’ of the sales department

We can achieve the same

SELECT * FROM
Employee
WHERE
Department_id = (Select department_id
FROM department
Where department_name =’Sales’);

The output of the above query will be:

Employee_ID Employee_Name Salary Department_Id Commission
101 Emp A 10000 1000
105 Emp E 32000 1000 10
106 Emp F 20000 1000 5

 

Here we can see that by using a SQL Subquery we have supplied the department_id parameter to the WHERE clause of the main SQL SELECT Statement.


Example 2: Using SQL Subquery as column in the main SQL SELECT Statement

Suppose we wish to view employee_id, employee_name, salary and department_name of all the employees who have salary less than 15000.

We can achieve the same by writing a SQL Subquery in sql select statement as follows:

SElECT e.employee_id,
e.employee_name,
salary,
(select d.department_name from department d
where e.department_id = d.department_id) Dept_name
from employee e
where
salary < 15000;

The output of the above query having a SQL Subquery will be:

 

Employee_ID Employee_Name Salary Dept_Name
101 Emp A 10000 Sales
108 Emp H 12000 Support

 

Here we can see that the SQL Subquery in the main SELECT statement becomes a column and we have aliased that column to DEPT_NAME


Example 3: Using SQL Subquery as the datasource for SELECT Statement:

Suppose we wish to view only ‘employee_id’ and ‘employee_name’ of employee who have ‘salary <15000’.

Reusing the SQL Subquery from example 2 we can achieve the same as

SELECT employee_id
, employee_name
FROM (select e.employee_id,
e.employee_name,
salary,
(select d.department_name from department d
where e.department_id = d.department_id) Dept_name
from employee e
where
salary < 15000);

The output of the above SQL Subquery will be:

Employee_ID Employee_Name
101 Emp A
108 Emp H

 Here we can see that by using the SQL Subquery from Example 2 we have increased the reusability of subquery in sql.

Important points about SQL Subqueries:

1) Oracle allows us to nest as many queries we want but it is recommended not to nest more than 16 sql subqueries.

2) If any sql subquery is not dependent on the outer query it is called a non-correlated subquery else it is called as correlated subquery in sql.


If you liked the above infomation then share the same with others also.

Thanks


Tagged , , , , , , , , . Bookmark the permalink.