15.00 - EXISTS / NOT EXISTS - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1145-015K
Language
English (United States)

EXISTS / NOT EXISTS

Purpose  

Tests a specified table (normally a derived table) for the existence of at least one row (that is, it tests whether the table in question is non-empty).

EXISTS is supported as the predicate of the search condition in a WHERE clause.

Syntax  

ANSI Compliance

EXISTS and NOT EXISTS are ANSI SQL:2011 compliant.

Usage Notes  

The function of the EXISTS predicate is to test the result of subquery.

If execution of the subquery returns response rows then the where condition is considered satisfied.

Note that use of the NOT qualifier for the EXISTS predicate reverses the sense of the test. Execution of the subquery does not, in fact, return any response rows. Instead, it returns a boolean result to indicate whether responses would or would not have been returned had they been requested.

Subquery Restrictions

The subquery cannot specify a SELECT AND CONSUME statement.

Relationship Between EXISTS/NOT EXISTS and IN/NOT IN

EXISTS predicate tests the existence of specified rows of a subquery. In general, EXISTS can be used to replace comparisons with IN and NOT EXISTS can be used to replace comparisons with NOT IN. However, the reverse is not true. Some problems can be solved only by using EXISTS and/or NOT EXISTS predicate. For an example, see “For ALL” on page 939.

For information on IN and NOT IN, see “IN/NOT IN” on page 943.

Example  

To select rows of t1 whose values in column x1 are equal to the value in column x2 of t2, one of the following queries can be used:

   SELECT * 
   FROM t1 
   WHERE x1 IN 
    (SELECT x2 
     FROM t2);
   SELECT * 
   FROM t1 
   WHERE EXISTS 
    (SELECT * 
     FROM t2 
     WHERE t1.x1=t2.x2);

To select rows of t1 whose values in column x1 are not equal to any value in column x2 of t2, you can use any one of the following queries:

   SELECT * 
   FROM t1 
   WHERE x1 NOT IN 
    (SELECT x2 
     FROM t2);
   
   SELECT * 
   FROM t1 
   WHERE NOT EXISTS 
    (SELECT * 
     FROM t2 
     WHERE t1.x1=t2.x2);
   
   SELECT 'T1 is not empty' 
   WHERE EXISTS 
    (SELECT * 
     FROM t1); 
   
   SELECT 'T1 is empty' 
   WHERE NOT EXISTS 
    (SELECT * 
     FROM t1);

EXISTS Predicate Versus NOT IN and Nulls

Use the NOT EXISTS predicate instead of NOT IN if the following conditions are true:

  • Some column of the NOT IN condition is defined as nullable.
  • Any rows from the main query with a null in any column of the NOT IN condition should always be returned.
  • Any nulls returned in the select list of the subquery should not prevent any rows from the main query from being returned.
  • For example, if all of the previous conditions are true for the following query, use NOT EXISTS instead of NOT IN:

       SELECT dept, DeptName 
       FROM Department 
       WHERE Dept NOT IN 
        (SELECT Dept 
         FROM Course);

    The NOT EXISTS version looks like this:

       SELECT dept, DeptName
       FROM Department 
       WHERE NOT EXISTS
        (SELECT Dept 
         FROM Course 
         WHERE Course.Dept=Department.Dept);

    That is, either Course.Dept or Department.Dept is nullable and a row from Department with a null for Dept should be returned and a null in Course.Dept should not prevent rows from Department from being returned.

    For ALL

    Two nested NOT EXISTS can be used to express a SELECT statement that embodies the notion of “for all (logical ­∀) the values in a column, there exists (logical ∃) …”

    For example the query to select a ‘true’ value if the library has at least one book for all the publishers can be expressed as follows:

       SELECT 'TRUE' 
       WHERE NOT EXISTS
        (SELECT * 
         FROM publisher pb 
         WHERE NOT EXISTS
          (SELECT * 
           FROM book bk 
           WHERE pb.PubNum=bk.PubNum);

    NOT EXISTS Clauses and Stored Procedures

    You cannot specify a NOT EXISTS clause in a stored procedure conditional expression if that expression also references an alias for a local variable, parameter, or cursor.

    NOT EXISTS and Recursive Queries

    NOT EXISTS cannot appear in a recursive statement of a recursive query. However, a non-recursive seed statement in a recursive query can specify the NOT EXISTS predicate.

    Example : EXISTS with Correlated Subqueries

    Select all student names who have registered in at least one class offered by some department.

       SELECT SName, SNo 
       FROM student s 
       WHERE EXISTS 
        (SELECT * 
         FROM department d 
         WHERE EXISTS 
          (SELECT * 
           FROM course c, registration r, class cl 
           WHERE c.Dept=d.Dept 
           AND c.CNo=r.CNo 
           AND s.SNo=r.SNo
           AND r.CNo=cl.CNo 
           AND r.Sec=cl.Sec));

    The content of the student table is as follows:

     

    Sname

    SNo

    Helen Chu

    1

    Alice Clark

    2

    Kathy Kim

    3

    Tom Brown

    4

    The content of the department table is as follows:

     

    Dept

    DeptName

    100

    Computer Science

    200

    Physic

    300

    Math

    400

    Science

    The content of course table is as follows:

     

    CNo

    Dept

    10

    100

    11

    100

    12

    200

    13

    200

    14

    300

    The content of the class table is as follows:

     

    CNo

    Sec

    10

    1

    11

    1

    12

    1

    13

    1

    14

    1

    The content of the registration table is as follows:

     

    CNo

    SNo

    Sec

    10

    1

    1

    10

    2

    1

    11

    3

    1

    12

    1

    1

    13

    2

    1

    14

    1

    1

    The following rows are returned:

       SName              SNo
       -----------        ---
       Helen Chu1           *
       Alice Clark          2
       Kathy Kim            3

    For a full explanation of correlated subqueries, see “Correlated Subqueries” in SQL Data Manipulation Language.

    Example : NOT EXISTS with Correlated Subqueries

    Select the names of all students who have registered in at least one class offered by each department that offers a course.

       SELECT SName, SNo 
       FROM student s
       WHERE NOT EXISTS
        (SELECT * 
         FROM department d 
         WHERE d.Dept IN 
          (SELECT Dept 
           FROM course) AND NOT EXISTS
            (SELECT * 
             FROM course c, registration r, class cl 
             WHERE c.Dept=d.Dept 
             AND c.CNo=r.CNo 
             AND s.SNo=r.SNo
             AND r.CNo=cl.CNo 
             AND r.Sec=cl.Sec)));

    With the contents of the tables as in “Example 1: EXISTS with Correlated Subqueries” on page 939, the following rows are returned:

       SName          SNo
       -----          ---
       Helen Chu      1