Querying the subtable

Find out the first companies that the employees worked for.


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.


1 =demo.query("select * from employee").new(name,resume.minp(start_date).company:first_company)

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.

In order to simplify the SQL statement as much as possible in the examples, the window functions of SQL 2003 standard are widely used, and accordingly the Oracle database syntax which has best support for SQL 2003 is adopted in this essay.