EXISTS/NOT EXISTS Predicate Examples | Teradata Vantage - Example: EXISTS with Correlated Subqueries - Analytics Database - Teradata Vantage

SQL Functions, Expressions, and Predicates

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-01-12
dita:mapPath
obm1628111499646.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
kby1472250656485
lifecycle
latest
Product Category
Teradata Vantageā„¢

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