SQL-J Language Reference
Page 43 of 121

Query

A query creates a virtual table based on existing tables or constants built into tables.

Syntax

{
    ( Query ) |
     Query UNION [ALL] Query |
     SelectExpression
     VALUES expression
}

You can arbitrarily put parentheses around queries, or use the parentheses to control the order of evaluation of UNION operations. UNION operations are evaluated from left to right when no parentheses are present.

You can combine two queries into one using the UNION [ALL] operation. UNION builds an intermediate ResultSet with all of the rows from both queries and eliminates the duplicate rows before returning the remaining rows. UNION ALL returns all rows from both queries as the result.

UNION (not UNION ALL) is not permitted with Java data types unless they are orderable. For information, see Orderable Java Data Types.

Example

-- a Select expression
SELECT *
FROM Hotels

-- a subquery
SELECT *
FROM (SELECT hotel_id FROM Hotels) AS SQ

-- a UNION
-- returns all rows from columns number_kids and number_adults
-- in table Groups
-- and (1,2) and (3,4)
-- number_kids and must be integer columns
SELECT number_kids, number_adults
FROM Groups
UNION ALL
VALUES (1,2), (3,4)

VALUES (1,2,3)