EXISTS Teradata

Another powerful resource that can be used with a correlated subquery is the EXISTS. It provides a true-false test within the WHERE clause.

In the syntax that follows, it is used to test whether or not a single row is returned from the subquery SELECT:

If a row is found, the EXISTS test is true, and conversely, if a row is not found, the result is false. When a true condition is determined, the value in the SELECT is returned from the main query. When the condition is determined to be false, no rows are selected.

Since EXISTS returns one or no rows, it is a fast way to determine whether or not a condition is present within one or more database tables. The correlated subquery can also be part of a join to add another level of test. It has potential to be very sophisticated.

As an example, to find all customers that have not placed an order the NOT IN subquery can be used. Remember, when you use the NOT IN clause the NULL needs to be considered and eliminated using the IS NOT NULL check in the subquery. When using the NOT EXISTS with a correlated subquery, the same answer is obtained, it is faster than a normal subquery and there is no concern for getting a null into the subquery. These next examples show the EXISTS and the NOT EXISTS tests.

Notice that the next SELECT is the same correlated subquery as seen earlier, except here it is utilizing the subquery to find all customers with orders:

4 Rows Returned

Customer name

By changing the EXISTS to a NOT EXISTS, the next SELECT finds all customers without orders:

1 Row ReturnedCustomer name

Since the Customer and Order tables are used in the above Correlated subquery, the table names did not require an alias. However, it was done to shorten the names to ease the equality coding in the subquery.

An added benefit of this technique (NOT EXISTS) is that the presence of a NULL does not affect the performance. Notice that in both subqueries, the asterisk (*) is used for the columns. Since it is a true or false test, the columns are not used and it is the shortest way to code the SELECT. If the column in the subquery table is a Primary Index or a Unique Secondary Index, the correlated subquery can be very fast.
The examples in this chapter only use a single column for the correlation. However, it is common to use more than one column from the main query in the correlated subquery. Although the techniques presented in this seem relatively simple, they can be very powerful. Understanding subqueries and Correlated subqueries can help you unleash the power.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Teradata Topics