EXISTS
Exists function is a test for existence. This is a logical test for the return of rows from a query.
Ex:
Suppose we want to display the department numbers which has more than 4 employees.
SQL> select deptno,count(*) from emp group by deptno having count(*) > 4;
DEPTNO COUNT(*)
--------- ----------
20 5
30 6
From the above query can you want to display the names of employees?
SQL> select deptno,ename, count(*) from emp group by deptno,ename having count(*) > 4;
No rows selected
The above query returns nothing because combination of deptno and ename never return more than one count.
The solution is to use exists which follows.
SQL> select deptno,ename from emp e1 where exists (select * from emp e2
where e1.deptno=e2.deptno group by e2.deptno having count(e2.ename) > 4) order by deptno,ename;
DEPTNO ENAME
---------- ----------
20 ADAMS
20 FORD
20 JONES
20 SCOTT
20 SMITH
30 ALLEN
30 BLAKE
30 JAMES
30 MARTIN
30 TURNER
30 WARD
NOT EXISTS
SQL> select deptno,ename from emp e1 where not exists (select * from emp e2
where e1.deptno=e2.deptno group by e2.deptno having count(e2.ename) > 4) order by deptno,ename;
DEPTNO ENAME
--------- ----------
10 CLARK
10 KING
10 MILLER
Hi There,
ReplyDeleteThank you! Thank you! Thank you! Your blog was a total game changer!
I try to calculate MAT (Moving Annual Total) in my Finance Multi-dimensional cube with the below. It is not working or not calculating. But when I replace the Aggregate with Sum, it worked but wrong numbers. Need your help on finding the work around. I tried couple of options from internet
Once again thanks for your tutorial.
Thanks,
David
Hello There,
ReplyDeleteGrazie! Grazie! Grazie! Your blog is indeed quite interesting around Exists function is a test for existence.! I agree with you on lot of points!
how to get the first non space character from a string in Oracle Could you help me on this?
Anyways great write up, your efforts are much appreciated.
Many Thanks,
Daniel
Hello Dayakar,
ReplyDeleteThree cheers to you ! Hooray!!! I feel like I hit the jackpot on SQL Exists !
I am trying to write a PLSQL block that firstly..
grabs the table name from all_tables where owner='rob1' and rownum =1
I then want to assign this table_name to a variable.
I then want to count the number of rows of this table and print to the screen.
I have pasted my code below. Are you able to reference a variable withing a sql statement as I have?
DECLARE
TABLE_HOLDER VARCHAR2(200);
COUNT_OF_ROWS NUMBER;
BEGIN
SELECT TABLE_NAME INTO TABLE_HOLDER FROM ALL_TABLES
WHERE OWNER ='ROB1'AND ROWNUM=1;
DBMS_OUTPUT.PUT_LINE(TABLE_HOLDER);
SELECT COUNT(*)INTO COUNT_OF_ROWS FROM TABLE_HOLDER;
DBMS_OUTPUT.PUT_LINE(COUNT_OF_ROWS);
END;
It was cool to see your article pop up in my google search for the process yesterday. Great Guide.
Keep up the good work!
Kind Regards,
Preethi.