Combining tables with different structures
Find out the average age of female employees (including both regular and temporary employees). The regular_employee and temp_employee tables may have different data structures.
SQL
SELECT avg(age)
FROM (SELECT age,gender FROM regular_employee UNION ALL
SELECT age,gender FROM temp_employee)
WHERE gender='female'
The SQL union all operator cannot union tables of different structures. The user will need to use the subquery to select fields of the same data structure from the tables first, which leads to data query duplication.
SPL
| A | |
| 1 | =demo.query("select * from regular_employee") |
| 2 | =demo.query("select * from temp_employee") |
| 3 | =A1|A2 |
| 4 | =A3.select(gender:"female").avg(age) |
SPL supports the union of result sets without requiring the same data structure, so it is simpler to code and faster to compute.
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.
