Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

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);

Comments

Popular posts from this blog

PostgreSQL pgBadger

PostgreSQL Pgbadger Installation On Linux

How to configure Replication Manager (repmgr) ?

Migrating From Oracle to PostgreSQL using ora2pg open source tools

7 Steps to configure BDR replication in postgresql