Querying the subtable

SQL

Multi-table join

SELECT name,company first_company
FROM (SELECT employee.name name,resume.company company,
             row_number() OVER(PARTITION BY resume.name
            ORDER BY resume.start_date) work_order
      FROM employee,resume WHERE employee.name=resume.name)
WHERE work_order=1

The subquery

SELECT name,
     (SELECT company FROM resume
       WHERE name=A.name
             AND start_date=(SELECT min(start_date)
                             FROM resume WHERE name=A.name)) first_company
FROM employee A

SQL cannot treat the subtable record sets as the attribute (field) of the main table, so the query on subtable must either be changed to multi-table join or use subquery to temporarily compute the subtable every time as needed. The program is very complicated to code and inefficient to compute.

SPL

SPL supports using the subtable record sets as fields of the main table, and accesses them as the other fields, so there is no need to repeatedly compute the subtables.