Example : Combining Equality and Inequality Conditions in a Correlated Subquery
You can use correlated subqueries to specify a combination of equality and inequality constraints with the subquery.
For example, to select the names of all students who are younger than all students at the same grade, you can perform the following query:
SELECT name
FROM student st1
WHERE age < ALL (SELECT age
FROM student st2
WHERE st1.grade = st2.grade
AND st1.stno <> st2.stno);
Example : Using SELECT COUNT(*) in a Correlated Subquery
Select the names of the publishers whose book count values in the library match the actual count of books.
SELECT pubname, bookcount
FROM library
WHERE (bookcount, pubnum) IN (SELECT COUNT(*), book.pubnum
FROM book
GROUP BY pubnum);
If the book count for a publisher in the library is zero, then the name of that publisher is not returned because no row is returned by the subquery for this publisher.
Note that the result data type for a COUNT operation is different for ANSI and Teradata session modes, as described by the following table. See SQL Functions, Operators, Expressions, and Predicates for details.
|
IN this session mode … |
THE data type of the result for a COUNT operation is … |
|
ANSI |
DECIMAL(p,0) where p represents the precision of the number. |
|
Teradata |
INTEGER |
Another equivalent SELECT statement uses two noncorrelated subqueries to return the same answer set as follows:
SELECT pub_name, book_count
FROM library
WHERE (book_count, pub_num) IN (SELECT COUNT(*), pub_num
FROM book
GROUP BY pub_num)
OR NOT IN (SELECT book.pub_num
FROM book
GROUP BY pub_num)
AND book_count = 0;
The following SELECT statement, which is less complicated and more elegant, uses a correlated subquery to return the same correct answer as the previous query.
SELECT pub_name, book_count
FROM library
WHERE book_count IN (SELECT count(*)
FROM book
WHERE book.pub_num = library.pub_num);
For more information about correlated subqueries, see: