Sub-Query in Select Clause

Sub-Query in select clause is nothing, but providing select statement in the place of column name.

It is also known as Scalar Query.

It is an Independent query.

Used to retrieve for data analysis reports.

Syntax:-

SQL>select , ,(<select statement>)from ;
 
Example:-
SQL>select empno, ename, sal, (select max (sal) from emp) maxsal from emp;
 
 
In this we have provided ‘SELECT STATEMENT’ in the place of column name which will return only single value.
If we try to return multiple values, system will raise an error which is shown below.
 
SQL> select empno, ename, sal, (select max (sal) from emp group by deptno) maxsal from emp;
 
Output:-
 
                
 
In the ‘SELECT STATEMENT’ we have provided ‘GROUP by’ which returns more values but the ‘SELECT STATEMENT’ which we have provided in the place of column name i.e. scalar query  will return only one value not more than that.
 
 
 
 
Example2:-
 
SQL>select empno,ename,sal,(select min(sal) from emp where job=e.job)lopay,(select max(sal) from emp where job=e.job)hipay from emp e order by job; 
 
                        
 
 
Example3:-
 
SQL>select empno, ename, sal, deptno, (select min (sal) from emp where deptno=e.deptno)losal,
(Select max (sal) from EMP where deptno=e.deptno) hisal from EMP e order by deptno;