Reference of the records associated by foreign key

Find out the male employees under the female managers.


Multi-table join:

FROM employee A,dept B,employee C
WHERE A.dept=B.dept
      AND A.gender='male'
      AND C.gender='female'

The subquery:

SELECT * FROM employee
WHERE gender='male' and dept in
     (SELECT dept FROM dept_table
     WHERE manager IN (SELECT name FROM employee
                       WHERE gender='female'))

Lacking a record referencing mechanism, SQL needs subquery or multi-table join to get the data field of the record associated by the foreign key, so it is tedious to code and inefficient to compute.


1 =demo.query("select * from employee").select(gender:"male",dept.manager.gender:"female")

With support for object reference, SPL can easily access the field of the record associated by the foreign key as its own attribute.

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.