SQL Select Set Operators

Set operators allow you to combine results from multiple select statements.

Syntax

select_clause [set_operator select_clause...][ORDER BY column direction [, column direction...]]
select_clause:   SELECT select_columns FROM table_name [WHERE expression] | (SELECT select_columns FROM table_name [WHERE expression])
set_operator:   UNION | UNION ALL | INTERSECT | EXCEPT

Examples

UNION

UNION returns a result set containing all the results from the left select statement and the right select statement. Only unique entries are returned.

Example


select id, membershipname from persons where id < 3
union select id,membershipname from memberships where id  < 3

result: [0, 1, 2]

UNION ALL

UNION ALL returns a result set containing all the results from the left select statement and the right select statement. All results are returned, including non-unique results.

Example


select id, membershipname from persons where id < 3
union all select id,membershipname from memberships where id  < 3
union all select id,membershipname from accounts where id < 3

result: [0, 1, 2, 0, 1, 2, 0, 1, 2]

INTERSECT

INTERSECT returns a result set containing all the records that are common to the left select statem0ent and the right select statement.

Example


select id, membershipname from persons where id < 3
intersect select id,membershipname from memberships where id  < 6
        
result: [0, 1, 2]

EXCEPT

EXCEPT returns a result set containing all the results from the left select statement except those found in the right select statement.

Example


select id, membershipname from persons where id < 6
except select id,membershipname from memberships where id >= 3 and id < 6
        
result: [0, 1, 2]

ORDER BY

If you want to sort all records across both select statements, include an order by expression. If order for this to work you must surround the select statements with parenthesis and place the order by expression outside the parenthesis. See the example

Example


(select id, membershipname from persons where id > 3 and id < 6)
union (select id,membershipname from memberships where id  < 3) order by id
        
result: [0, 1, 2, 4, 5]