What is EXIST, ANY, ALL in subqueries

fayum · New User Joined: 27 Feb 2004 Posts: 2 Location: chennai

1. pls let me known about the maximum no of columns& rows that can created in DB2

2. explain in detail about exist ,Any ,All in subqueries

.

Ananya · New User Joined: 26 Feb 2004 Posts: 21 Location: Chennai

Hi there

1. Max no of Cols and Rows in a DB2 Table.

the maximum number of columns in a table and the maximum byte size of a row in a table are determined by the page size.

Page Size Row Size Limit Column Count Limit

4K 4 005 500
8K 8 101 1 012
16K 16 239 1 012
32K 32 677 1 012

2. Exists, Any, All

You can use a subquery to test for the existence of a row that satisfies some condition. In this case, the subquery is linked to the outer-level query by the predicate EXISTS or NOT EXISTS.

When you link a subquery to an outer query by an EXISTS predicate, the subquery does not return a value. Rather, the EXISTS predicate is true if the answer set of the subquery contains one or more rows, and false if it contains no rows.

The EXISTS predicate is often used with correlated subqueries. The example below lists the departments that currently have no entries in the PROJECT table:

SELECT DEPTNO, DEPTNAME
FROM DEPARTMENT X
WHERE NOT EXISTS
(SELECT *
FROM PROJECT
WHERE DEPTNO = X.DEPTNO)
ORDER BY DEPTNO
You may connect the EXISTS and NOT EXISTS predicates to other predicates by using AND and OR in the WHERE clause of the outer-level query.

Quantified Predicates All & Any
A quantified predicate compares a value with a collection of values. If a fullselect returns more than one value, you must modify the comparison operators in your predicate by attaching the suffix ALL, ANY, or SOME. These suffixes determine how the set of values returned is to be treated in the outer-level predicate. The > comparison operator is used as an example (the remarks below apply to the other operators as well):

expression > ALL (fullselect)
The predicate is true if the expression is greater than each individual value returned by the fullselect. If the fullselect returns no values, the predicate is true. The result is false if the specified relationship is false for at least one value. Note that the <>ALL quantified predicate is equivalent to the NOT IN predicate.
The following example uses a subquery and a > ALL comparison to find the name and profession of all employees who earn more than all managers:

SELECT LASTNAME, JOB
FROM EMPLOYEE
WHERE SALARY > ALL
(SELECT SALARY
FROM EMPLOYEE
WHERE JOB='MANAGER')

expression > ANY (fullselect)
The predicate is true if the expression is greater than at least one of the values returned by the fullselect. If the fullselect returns no values, the predicate is false. Note that the =ANY quantified operator is equivalent to the IN predicate.

expression > SOME (fullselect)
SOME is synonymous with ANY.

Hope this Helps !!!

Cheers
-Ananya