Nested Tables (Cont.)

Oracle does not support equal match predicates between nested tables. To find employees with one specific dependent, the IN predicate can be used.

Question: List eids of employees with a dependent having a social security number 123822332.    


Question: Retrieve the number of dependents of employee 101.
  1. In the first query, the subquery retrieves a scalar nested table value, e.dependents, so the result of the count will be one or a null.
  2. The second query has wrong syntax.
  3. The last query correctly answers the question. 
SQL> SELECT  COUNT( * )  FROM ( 
  2    SELECT  e.dependents  FROM  employees e 
  3      WHERE  e.eid = 101 ); 

  COUNT(*)
----------
         1

SQL> SELECT  COUNT( * )  FROM ( 
  2    SELECT  TABLE( e.dependents )  FROM  employees e 
  3      WHERE  e.eid = 101 ); 

SELECT  TABLE(e.dependents)  FROM  employees e  WHERE  e.eid = 101)
        *
ERROR at line 2:
ORA-00936: missing expression

SQL> SELECT  COUNT( * )  FROM 
  2    TABLE( SELECT  e.dependents  FROM  employees e 
  3             WHERE  e.eid = 101 ); 

  COUNT(*)
----------
         2