Oracle Subquery

• Subquery or inner query or nested query is a query .sql subquery is usually added in the where clause of the sql statement.
•  The subquery can be nested inside a select,insert,update or delete statement or inside another query.
• You can use the comparison operators such as  > , < , or = . the comparison operator can also be a multiple-row operator such as  IN , ANY , or ALL.
• The inner query executes first before its parent query so that the result of inner query can be passed to the outer query.

Types Of Subqueries :-

1. Single row subquery
2. Multiple row subquery
3. Correlated subquery

SINGLE ROW SUBQUERY :-

 Queries that return only one row from the inner select statement.

Select statement :

SQL > select * from tablename where columnname=(select columnname from tablename where columnname = ’value’);

Example :-
 SQL > select * from t1 where id = (select id from t1 where salary =4500);

Insert statement :

Syntax :-
 SQL > insert into tablename1 select * from tablename2 where columnname = (select columnname from tablename2 where columnname=’value’);
Example :-
 SQL > insert into t1 select * from emp where empno = (select empno from emp where sal=800);

Update statement :

Syntax :-
 SQL > update tablename1 set columnname=value where columnname = (select * from  tablename where columnname=value);

Example :-
 SQL > update emp set sal=2000 where empno=(select * from t1where sal=800);

Delete statement :

Syntax :- 
 SQL >  delete from tablename where columnname =(select columnname from tablename where columnname=’value’);

Example :-
 SQL > delete from emp where empno=(select empno from t1 where ename=’SMITH’);

MULTI ROW SUBQUERY

 Queries that return more than one row from the Inner SELECT statement.

Types of operators

Operators	Meaning
In                                 Any       All	Equal to any member in the list Compare value to each value returned by the subquery Compare value to every value returned by the subquery

IN

 SQL > select * form tablename where columnname in (select columnname from tablename where columnname=’values’);
Example :-
 SQL > select * from emp where sal in (select sal from emp where deptno=10);

ANY

Syntax :-
 SQL > select * form tablename where columnname =any (select columnname from tablename where columnname=’values’);

Example :-
 SQL > select * from emp where sal =any(select sal from emp where deptno=20);

 You can use the ANY operator to compare a value with any value in a list. You must place an =, <>, >, <, <= or >= operator before ANY in your query

ALL

Syntax :-
 SQL > select * form tablename where columnname < all (select columnname from tablename where columnname=’values’);

Example :-
 SQL > select * from emp where sal < all(select sal from emp where deptno=20);

Correlated subqueries

 Correlated subqueries are used for row-by-row processing. Each subquery is executed once for every row of the outer query.

 Syntax :-
 SQL > select columnname,columnname from table1  where columnname operator (select columnname, from table2   where columnname=’values’);

Example :-
 SQL > select a.name,a.salary from emp a where a.deptno =(select  deptno from dept b where b.deptno=10);