HSQL DB is not accepting UNION in query

Question

I'm using hsql db(v:2.3.6) and query is

select id, name, phone
from person p, address a
where a.id = p.id 
order by id LIMIT 10 

union 

select id, name, phone
from old_person op, old_address oa
where op.id = oa.id
order by id LIMIT 10

But above query is throwing an error :

Caused by: org.hsqldb.HsqlException: unexpected token: UNION

I'm not sure why this is an issue.

Answer 1

Your query is intended to get 10 rows from the person table and 10 rows from the old_person table before merging the lists in UNION. For this, you need to use parentheses around each SELECT query.

(select id, name, phone
from person p, address a
where a.id = p.id 
order by id LIMIT 10) 

union 

(select id, name, phone
from old_person op, old_address oa
where op.id = oa.id
order by id LIMIT 10)

If you remove the first LIMIT and keep the last one, you may get fewer rows (which could also be different rows) as the total limit is reduced from 20 to 10.