How to Write Simple Subqueries in SQL?

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.