Saturday, 2 April 2016

SQL Exists

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

1. Hi There,

Thank 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

2. Hello There,

Grazie! 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

3. Hello Dayakar,

Three 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.

4. Oracle apps and Fusion Self Paced Training Videos by Industry Experts. Please Check oracleappstechnical.com