SQL Sub-Queries explained

 Sub-Query

 A sub-query is a query that is used within another query to retrieve data that will be used to manipulate the result of the main query. Here the output of inner query will be the input for outer query.It is also known as nested query or inner query.

There are 3 main types of sub-query: -

1. Single-row sub-query: - A single row sub-query is a query in which inner query returns(or output) only row of data which  will be provided as an input to the outer query. To pass output of inner query output to outer query input we use '=' operator.

 

2. Multi-row sub-query: - A multi-row sub-query is a query where we get multiple output from inner query and those output will be passed as input to the outer query. To pass output of inner query output to outer query input we use 'IN' operator.

3. Co-related sub-query: - Co-related sub-query is a query that uses values from the outer query to filter or manipulate the result of the sub-query. It used to retrieve data that is related to current row being processed in the main query. Co-related sub-queries are used for row by row processing."ROWNUM" is a pseudo-column that is used to assign a unique sequential number to each row returned by a query.

Now we will perform the practical example for all these query. We will use oracle 10g scott schema and EMP table for practical purpose. Below is the output of EMP table.

Dept Table:- 

 

Single-row sub-query Example: -

1. Write a query to display all the of employee whose job is same as JAMES: -
select * from emp where job=(select job from emp where ename='JAMES');

2. write a query to display all the employee details who is earning salary equal or more than TURNER: -
select * from emp where sal>=(select sal from emp where ename='TURNER');

 

3. Write a query to display list of all the employee working in SALES department: -

select * from emp where deptno=(select deptno from dept where DNAME='SALES');

 

Multi-row sub-query Example: -

1. Display all the employee details who is either working for SALES or RESEARCH department: -

 select * from emp where deptno in(select deptno from dept where DNAME='SALES' or DNAME='RESEARSH);

2. Display longest employee name present in emp table: -
select * from emp where length(ename) in(select max(length(ename)) from emp);

Co-related sub-query Example: -

1. write a query to display 3rd record from emp table.
select * from (select emp.* , rownum jsp from emp) where jsp =3;

 

 

Thank you for reading the post. In the next blog I will provide more query practice and solutions based on sub-query and Joins.