In this article, we will see what is sub query in SQL and its use. Also we will write simple sub queries with practical examples.
In simple words a sub query is nothing but a query inside query.
Many times you would like to have a series of SQL chained where an output of one query is sent an input to the other query for filtering and manipulation.
For example, you can see the figures here one table has a customer sales amount and another table has a customer address.
Here I would like to get the customers whose Sales Amount is greater than 1500 and along with their address.
Customer Sales Table
Customer Address Table
Output of the query should be as below
Now let us write a inner query for this requirement.
Select Customer ID from Customer Sales where Sales Amount >1500
This is our inner query. Now the output of the inner query will be fed to the outer query which will actually go and extract customer address.
Select Customer ID, Address From Customer Address Where Customer ID IN (Select Customer ID from Customer Sales Where Sales Amount>1500)
Now here inner query will evaluate first. It will extract whose Customer Sales Amount is greater than in 1500. Those IDs then fed to the outer query. The address will be captured to the Ids which have been supplied by the inner query.
Let us see another sub-query example here.
Consider employee table. I want to get the department wise maximum of salary using sub query.
Here we will how we can achieve that.
Select department ID, salary from employee where salary IN ( select max (salary) from employee group by department ID)
Now let us write another sub query to achieve second highest salary from employee table.
Select max(salary) from employee where salary NOT IN (Select max (salary) from employee)
If you can observe the above query second select statement in the query is called our inner query which will pass the result set to the outer query.
What happens here when we execute the query?
First, our inner query will be executed it ignores the max of salary from the employee table and pass the results to the outer query.
What outer query will do here? From the available result set it will capture the max of salary.
Hence it will returns the second highest salary from the employee table.
How to join two sub queries in SQL?
Here we will see SQL sub-query multiple tables and how to join those tables using simple method.
consider the below small query for this example
select empno,ename,salary from emp where deptno=10
Now, this quey is my inner query and I am going to join this query with another table (alias table). Make some alias name for this query as table A.
select location.ID, Location.name, B.maxsalary from ( select max(A.salary) as maxsalary, department.name from (select empno,ename,salary from emp where deptno=10)A where A.deptno=department.deptno)B where Location.Location ID = B.Location ID
Now here you can consider the above query as alias table B and then you join this table with any other table in case you have matching join between the tables.
( select max(A.salary) as maxsalary, department.name from (select empno,ename,salary from emp where deptno=10)A where A.deptno=department.deptno)B
I can join this B table with location table.
Let us write a query for that.
let us consider that location ID in the employee table. Now I am going to join this table with the Location table based on location ID.
Now our query will be
select location.ID, Location.name, B.maxsalary from ( select max(A.salary) as maxsalary, department.name from (select empno,ename,salary from emp where deptno=10)A where A.deptno=department.deptno)B where Location.location ID = B.Location ID
The is one of the way how you can join sub queries in SQL Server.
Similar way you can join SQL multiple sub queries in (select statement).
I hope this article sub queries in SQL is useful to you. Please leave your comment in the comment section below.