SQL Subquery with examples

In this page we will discuss about Subquery in SQL with some examples. In earlier post we were discussed SQL group by and Having clause with examples. Today we will understand and learn how to create SQL Subquery and what purpose we used  Subquery in SQL.

Subquery is also known as Nested Query or inner query. In simple words we can say "query within query". The subquery returns a value that is used by the parent statement(outer query)

Subqueries can be used the following purpose:

• subqueries can be used for providing values for conditions in WHERE,HAVING and START with clauses of SELECT statements.
• It is also used to define set of rows to be included in a VIEW in a CREATE VIEW statements. 

The subquery(inner query) is executed first before main query(outer query).Here result of the query will be used by the outer query. The inner and outer queries can retrieve data from either same table or different tables.

The best way to understand this concept is to see in application and will take a employee table,based on this table we will create subquery and see some example about on it.


Create Employee table:

create table Employee
(

 Emp_Id int,
First_Name varchar2(20),
Last_Name varchar2(20),
salary number,
Department varchar2(20));

After creating table insert some data into that table like below result.

Now, let us see the highest salary in the Employee table and also want to find out who are getting that salary without using subquery.

select max(salary)from Employee;

Result:

If you want to see Id,name  of the Employees from the employee table then you need to write Query like below.

select Emp_Id,First_Name,Last_Name from Employee
where salary=20000;

Result:

Suppose, the above two queries combined to in single step, then you people think that to write a query like below

select Emp_id,First_Name,Last_Name from Employee
where salary=max(salary);

Result:

If you do write query like above then it will not give result. It shows above result.Because Max is a aggregate function,it doesn't have its own query as shown in the above query. In order to apply aggregate function on salary filed we have to insert other query inside parent query.

Now, Rewrite the preceding query by adding subquery like below

select Emp_id,First_Name,Last_Name from Employee
where salary=(select max(salary)from Employee);

Result:

Notice that above result who get highest salary from employee table with fields. Here select max(salary)from employee is the inner query or subquery for entire query.Firstly, inner query has executed then the result of this query used by parent query or outer query.

Now we will find out 2nd highest salary of employee from employee table using subquery

select First_Name,Last_Name,salary from Employee
where salary=(select max(salary)from Employee where salary<

(select max(salary)from EMployee));

Result:

Let us discuss how the above query will works, Firstly it will executes inner most query that means select max(salary)from Employee,then inner query will execute then we got that result.

Similarly to find 3rd highest salary of employees from Employee table using subquery then write query like below

select First_Name,Last_Name,salary from Employee
where salary=(select max(salary)from Employee where salary<

(select max(salary)from Employee where salary<
(select max(salary)from Employee)));

Result:

I hope you enjoy this post and keep follow me to get latest updates and share this post on social websites and also give comments on this concept.