Home » PL/SQL » How to use NOT EXISTS operator with correlated subquery in PL/SQL
How to use NOT EXISTS operator with correlated subquery in PL/SQL
By Thomas Mapother February 23, 2011 NOT EXISTS is used inside the subquery and returns NULL (no rows selected) the main query will return its rows accordingly, and viceversa.
As you learned previously in my article "Using Exists operator with correlated subquery in PL/SQL".
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 NOT EXISTS (sub query)
NOT EXISTS operator: NOT EXISTS works the opposite of EXISTS. The WHERE clause in NOT EXISTS is satisfied if no rows are returned by the subquery, when using NOT EXISTS if the subquery returns NULL (no rows selected) the main query will return its rows accordingly, and viceversa. The subquery is correlated and can be thought of as doing a lookup for each slected item in the selected table, the NOT EXISTS subquery returns at least one row then the main query will return no rows selected.
Syntax:
SELECT FROM TABLENAME
WHERE NOT EXISTS (sub query)
Example of correlated subquery with NOT EXISTS operator:
SQL> create table Workers(
2 Worker_Id NUMBER(3),
3 Worker_Name VARCHAR2(12 BYTE),
4 Joining_Date DATE,
5 Basic_Pay NUMBER(6),
6 Inhand NUMBER(6),
7 REGION VARCHAR2(1 BYTE),
8 Dept_Id NUMBER(3)
9 )
10 /
Table created.
SQL>
SQL> create table work (
2 Worker_Id NUMBER(3),
3 Profile VARCHAR2(20 BYTE)
4 )
5 /
Table created.
SQL>
SQL> insert into work (Worker_Id, Profile) values (1,'Tester');
1 row created.
SQL> insert into work (Worker_Id, Profile) values (2,'MIS');
1 row created.
SQL> insert into work (Worker_Id, Profile) values (3,'Developer');
1 row created.
SQL> insert into work (Worker_Id, Profile) values (4,'HR_dept');
1 row created.
SQL> insert into work (Worker_Id, Profile) values (5,'Supervisor');
1 row created.
SQL> insert into work (Worker_Id, Profile) values (6,'Mediator');
1 row created.
SQL> insert into work (Worker_Id, Profile) values (7,'Multimedia');
1 row created.
SQL> insert into work (Worker_Id, Profile) values (8,'Programmer');
1 row created.
SQL> insert into work (Worker_Id, Profile) values (9,'Developer');
1 row created.
SQL>
SQL>
SQL> -- prepare data
SQL> insert into Workers(Worker_Id, Worker_Name, Joining_Date, Basic_Pay, Inhand, State, Dept_Id)
2 values (1, 'Marin', to_date('19960725','YYYYMMDD'), 1234, 8767, 'E', 2)
3 /
1 row created.
SQL> insert into Workers(Worker_Id, Worker_Name, Joining_Date, Basic_Pay, Inhand, State, Dept_Id)
2 values (2, 'Colon', to_date('19970712','YYYYMMDD'), 2341, 3456, 'W', 3)
3 /
1 row created.
SQL> insert into Workers(Worker_Id, Worker_Name, Joining_Date, Basic_Pay, Inhand, State, Dept_Id)
2 values (3, 'Robort', to_date('19860125','YYYYMMDD'), 4321, 5654, 'E', 3)
3 /
1 row created.
SQL> insert into Workers(Worker_Id, Worker_Name, Joining_Date, Basic_Pay, Inhand, State, Dept_Id)
2 values (4, 'Dik', to_date('20060913','YYYYMMDD'), 2413, 6787, 'W', 4)
3 /
1 row created.
SQL> insert into Workers(Worker_Id, Worker_Name, Joining_Date, Basic_Pay, Inhand, State, Dept_Id)
2 values (5, 'Harry', to_date('20050420','YYYYMMDD'), 7654, 4345, 'E', 4)
3 /
1 row created.
SQL> insert into Workers(Worker_Id, Worker_Name, Joining_Date, Basic_Pay, Inhand, State, Dept_Id)
2 values (6, 'Nikolas', to_date('20040718','YYYYMMDD'), 5679, 6546, 'W', 5)
3 /
1 row created.
SQL> insert into Workers(Worker_Id, Worker_Name, Joining_Date, Basic_Pay, Inhand, State, Dept_Id)
2 values (7, 'Sparo', to_date('20030720','YYYYMMDD'), 5438, 7658, 'E', 6)
3 /
1 row created.
SQL> insert into Workers(Worker_Id, Worker_Name, Joining_Date, Basic_Pay, Inhand, State)
2 values (8, 'Roeli', to_date('20020101','YYYYMMDD'), 8765, 4543, 'W')
3 /
1 row created.
SQL> insert into Workers(Worker_Id, Worker_Name, Joining_Date, Basic_Pay, Inhand, State)
2 values (9, 'Haydan', to_date('20010829','YYYYMMDD'), 7896, 1232, 'E')
3 /
1 row created.
SQL>
SQL> -- display data in the table
SQL> select * from Workers
2 /
Worker_Id Worker_Name Joining_Date Basic_Pay Inhand REGION Dept_Id
---------- --------------- ------------- ----------- ----------- -------- ----------
1 Marin 25-DEC-96 1234 8767 E 2
2 Colon 12-DEC-97 2341 3456 W 3
3 Robort 25-FEB-86 4321 5654 E 3
4 Dik 13-SEP-06 2413 6787 W 4
5 Harry 20-APR-05 7654 4345 E 4
6 Nikolas 18-DEC-04 5679 6546 W 5
7 Sparo 20-DEC-03 5438 7658 E 6
8 Roeli 01-FEB-02 8765 4543 W
9 Haydan 29-AUG-01 7896 1232 E
9 rows selected.
SQL> select * from work
2 /
Worker_Id Profile
---------- ----------
1 Tester
2 MIS
3 Developer
4 HR_dept
5 Supervisor
6 Mediator
7 Multimedia
8 Programmer
9 Developer
9 rows selected.
SQL>
SQL> SELECT Worker_Id, Worker_Name
2 FROM Workers outer
3 WHERE NOT EXISTS
4 (SELECT 1
5 FROM Workers inner
6 WHERE inner.Worker_Id = outer.Dept_Id);
Worker_Id Worker_Name
---------- ---------------
9 Haydan
8 Roeli
Happy Learning.
Login to add your contents and source code to this article C# Consulting is founded in 2002 by the founders of C# Corner. Unlike a traditional consulting company, our consultants are well-known experts in .NET and many of them are MVPs, authors, and trainers. We specialize in Microsoft .NET development and utilize Agile Development and Extreme Programming practices to provide fast pace quick turnaround results. Our software development model is a mix of Agile Development, traditional SDLC, and Waterfall models.
0 comments:
Post a Comment