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:
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.
We Will be looking at:
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:
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:
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:
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:
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.