Sub-Query in WHERE clause

Sub-Query in WHERE clause is acts like normal sub-query.

Syntax:-

SQL>select ,  from  where   (select  from where );  
 
Column1, column2, column3, column4—column names
Table name – database object name
Conditional expression – in or exists or = or < or > or ! =
*Column3,column4 must be of same datatype.
 
Example:-
Display the employees who are working in location ‘CHICAGO’
SQL>select empno, ename, sal, deptno from emp where deptno in (select deptno from dept where loc=’CHICAGO’);
 
 
 
Example 2:-
 
Display the employees whose salary is less than average pay
 
SQL>select empno, ename, deptno from emp where sal < (select avg (sal) from emp) order by sal);
 
 
 
 
Example 3:-
 
Raise the salary by 20% for the employees whose salary is less than Average salary
 
SQL>update emp set sal=sal+sal*.25 where sal <(select avg(sal) from emp);
 
-- 8 Rows updated