Effectively Using Exists operator with correlated subquery in PL/SQL Asp.Net C#

As you learned previously in my article "Using correlated subquery in PL/SQL". Correlated subquery: 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.

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.

Syntex:

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

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

Exists Keyword: The PL/SQ EXISTS keyword is an operator which is used to correlate records from two participating queries it returns TRUE if the nth element in a collection exists. Otherwise, EXISTS(n) returns FALSE. Mainly, you use EXISTS with DELETE to maintain sparse nested tables. One of the participating query is an outer SELECT statement, with the other one being an inner sub query it is also use EXISTS to avoid raising an exception when you reference a nonexistent element, It returns a BOOLEAN output which specifies the result of logical comparison of two records matching on defined set of values.

Syntax:

SELECT  FROM TABLENAME

WHERE EXISTS (sub query)

Example of correlated subquery with Exists operator:

SQL> CREATE TABLE Worker(
2      WorkerNO NUMBER(4) NOT NULL,
3      Name VARCHAR2(10),
4      Profile VARCHAR2(9),
5      Batch_No NUMBER(4),
6      Joining_date DATE,
7      Salary NUMBER(7, 2),
8      Extra_bonus NUMBER(7, 2),
9      Dept_id NUMBER(2)
10  );
Table created. 
SQL> INSERT INTO Worker VALUES(2, 'mariya', 'Tester', 6,TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600, 300, 30);
1 row created.
SQL> INSERT INTO Worker VALUES(3, 'solana', 'Tester', 6,TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250, 500, 30);
1 row created.
SQL> INSERT INTO Worker VALUES(4, 'nailson', 'Designer', 9,TO_DATE('2-APR-1981', 'DD-MON-YYYY'), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO Worker VALUES(5, 'cristna', 'Tester', 6,TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO Worker VALUES(6, 'rosi', 'Designer', 9,TO_DATE('1-MAY-1981', 'DD-MON-YYYY'), 2850, NULL, 30);
1 row created.
SQL> INSERT INTO Worker VALUES(7, 'gaeri', 'Designer', 9,TO_DATE('9-JUN-1981', 'DD-MON-YYYY'), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO Worker VALUES(8, 'brad', 'Helper', 4,TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO Worker VALUES(9, 'pollson', 'Manager', NULL,TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO Worker VALUES(10, 'tailor', 'Tester', 6,TO_DATE('8-SEP-1981', 'DD-MON-YYYY'), 1500, 0, 30);
1 row created. 
SQL> INSERT INTO Worker VALUES(13, 'raison', 'Helper', 4,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 3000, NULL, 20);
1 row created. 
SQL>
SQL> CREATE TABLE Department(
2      Dept_id NUMBER(2),
3      Dept_name VARCHAR2(14),
4      State VARCHAR2(13)
5  );
Table created.
SQL>
SQL> INSERT INTO Department VALUES (10, 'ACCOUNTING', 'NEW YORK');
1 row created.
SQL> INSERT INTO Department VALUES (20, 'PURCHASING', 'DALLAS');
1 row created.
SQL> INSERT INTO Department VALUES (30, 'SalaryES', 'CHICAGO');
1 row created.
SQL> INSERT INTO Department VALUES (40, 'OPERATIONS', 'BOSTON');
1 row created.
SQL>
SQL>
SQL> SELECT e1.Name, d.Dept_name
2      FROM   Worker e1, Department d
3      WHERE  e1.Dept_id = d.Dept_id
4      AND EXISTS
5      (SELECT 'x' FROM Worker e2
6      WHERE  e2.Name = 'brad' AND    e2.Dept_id = e1.Dept_id)
7  /
Name          Dept_name
--------    --------------
nailson       PURCHASING
brad          PURCHASING
raison        PURCHASING

Hope this helps,happy coding.