Creating Using Querying correlated subquery in PL/SQL Asp.Net C#

Introduction

The first thing which you may know, that is what is correlated subquery, how to use that and why we use this queries in PL/SQL. So, When you reference a column from the table in the parent query in the subquery, it is known as a correlated subquery, they are used for row-by-row processing. Each subquery is executed once for every row of the outer query, the inner query uses information from the outer query and executes once for every row in the outer query. This correlation is accomplished by using a reference to the outside query within the subquery. It is one way of reading every row in a table and comparing the values in each row against the related data.

Usage of correlated subquery

The use of a correlated subquery is not very efficient. Using joins rather than a correlated subquery enables the optimizer to determine how to correlate the data in the most efficient way. A practical use of a correlated subquery is to transfer data from one table to another. However, be misled into thinking that the database optimizer actually executes it in that fashion, because this will lead you to believe that it's not very efficient, when in fact most optimizers will process a correlated subquery very efficiently, as a join.

Difference between simple subquery and correlated subquery

what's the difference between an ordinary subquery and a correlated subquery: The correlated subquery makes an actual reference, using a correlation variable, to the outer query and in the simple sub-query the inner query is executed in entirety before the outer query is executed. And in correlated subquery, for each record fetched in outer query corresponding matching records are fetched in sub-query because of join condition within inner sub-query.

Syntex:

SELECT id, Worker_name, salary FROM Worker outer WHERE salary > (SELECT AVG(salary)

FROM Worker inner WHERE inner.id = outer. id);

Complete Example:

SQL> create table Worker(
2    ID                       VARCHAR2(4 BYTE)         NOT NULL,
3    Worker_Name        VARCHAR2(10 BYTE),
4    S_Date                DATE,
5    L_Date                DATE,
6    Salary                 Number(8,2),
7    City                    VARCHAR2(10 BYTE),
8    Description           VARCHAR2(15 BYTE)
9  )
10  /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into Worker(ID,  Worker_Name,  S_Date,                     L_Date,                       Salary,  City,       Description)
2               values ('01','manis',    to_date('19960725','YYYYMMDD'), to_date('20060725','YYYYMMDD'), 1234.56, 'Toronto',  'Programmer')
3  /
1 row created.
SQL> insert into Worker(ID,  Worker_Name, S_Date,                     L_Date,                       Salary,  City,       Description)
2                values('02','Rahul',  to_date('19760321','YYYYMMDD'), to_date('19860221','YYYYMMDD'), 6661.78, 'Vancouver','Tester')
3  /
1 row created.
SQL> insert into Worker(ID,  Worker_Name, S_Date,                     L_Date,                       Salary,  City,       Description)
2                values('03','Ravi',   to_date('19781212','YYYYMMDD'), to_date('19900315','YYYYMMDD'), 6544.78, 'Vancouver','Tester')
3  /
1 row created.
SQL> insert into Worker(ID,  Worker_Name, S_Date,                     L_Date,                       Salary,  City,       Description)
2                values('04','pall',   to_date('19821024','YYYYMMDD'), to_date('19990421','YYYYMMDD'), 2344.78, 'Vancouver','Manager')
3  /
1 row created.
SQL> insert into Worker(ID,  Worker_Name, S_Date,                     L_Date,                       Salary,  City,       Description)
2                values('05','peter',  to_date('19840115','YYYYMMDD'), to_date('19980808','YYYYMMDD'), 2334.78, 'Vancouver','Tester')
3  /
1 row created.
SQL> insert into Worker(ID,  Worker_Name, S_Date,                     L_Date,                       Salary, City,        Description)
2                values('06','shinda',   to_date('19870730','YYYYMMDD'), to_date('19960104','YYYYMMDD'), 4322.78,'New York',  'Tester')
3  /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Worker
2  /
ID   Worker_NAME   S_DATE    L_DATE     SALARY      CITY     DESCRIPTION
---- ----------   ---------   ---------- ---------- -------   ------------
01   manis         25-JUL-96   25-JUL-06    1234.56   Toronto      Programmer
02   Rahul         21-MAR-76  21-FEB-86    6661.78   Vancouver  Tester
03   Ravi          12-DEC-78   15-MAR-90   6544.78   Vancouver  Tester
04   pall           24-OCT-82   21-APR-99   2344.78   Vancouver  Manager
05   peter        15-JAN-84    08-AUG-98   2334.78   Vancouver  Tester
06   shinda       30-JUL-87    04-JAN-96   4322.78    New York   Tester
6 rows selected.
SQL>
SQL>
SQL> SELECT id, Worker_name, salary
2  FROM Worker outer
3  WHERE salary >
4    (SELECT AVG(salary)
5     FROM Worker inner
6     WHERE inner.id = outer. id);

 no rows selected

Hope this helps,happy coding.