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 :-
Single row subquery
Multiple row subquery
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
You can use the ANY operator to compare a value with any value in a list. You must place an =, , >, <, = operator before ANY in your query
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);
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);
postgres 
