EXISTS and NOT EXISTS SQL

 EXISTS:

   v The EXISTS operator is used check for the existence of any record in a sub query.

   v EXISTS query returns TRUE if there at least one tuple in the result of the sub query.

Syntax:

1.  SELECT column_list

    FROM table_name

    WHERE EXISTS

    (SELECT column_name from table_name where <condition>); 

Example:

1. SELECT deptno, ename

     FROM emp e

    WHERE EXISTS (SELECT deptno

                    FROM dept d

                   WHERE d.deptno = e.deptno AND dname IS NOT NULL);

Output:

NOT EXISTS:

v The NOT EXISTS operator is opposite to the EXISTS operator, It checks the sub query for rows existence and if the sub query of the result has no rows then it will return TRUE otherwise false.

Syntax:

1. SELECT column_list

    FROM table_name

    WHERE  NOT EXISTS

    (SELECT column_name from table_name where <condition>); 

Example:

1. SELECT deptno, ename

     FROM emp e

    WHERE NOT EXISTS (SELECT deptno

                    FROM dept d

                   WHERE d.deptno = e.deptno AND dname IS NOT NULL);

Output:

Share: