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
By changing the EXISTS to a NOT EXISTS, the next SELECT finds all customers without orders:
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.
Teradata Related Interview Questions
|Microstrategy Interview Questions||Informatica Interview Questions|
|MySQL Interview Questions||Oracle 11g Interview Questions|
|Hadoop Interview Questions||TeraData DBA Interview Questions|
|MYSQL DBA Interview Questions||Database Administration Interview Questions|
|DB2 SQL Programming Interview Questions||Hadoop Administration Interview Questions|
|Java Hadoop Developer Interview Questions||Informatica MDM Interview Questions|
|Informatica Admin Interview Questions||Hadoop Testing Interview Questions|
Teradata Related Practice Tests
|Microstrategy Practice Tests||Informatica Practice Tests|
|MySQL Practice Tests||Oracle 11g Practice Tests|
|Hadoop Practice Tests||TeraData DBA Practice Tests|
|MYSQL DBA Practice Tests||Database Administration Practice Tests|
|DB2 SQL Programming Practice Tests||Hadoop Administration Practice Tests|
Teradata Parallel Architecture
Fundamental Sql Using Select
On-line Help And Show Commands
Date And Time Processing
Character String Processing
Reporting Totals And Subtotals
Data Definition Language
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.