|
Depending on context, USoft partly supports standard SQL set operator syntax, as follows. For details on syntax, semantics and nesting, consult any online SQL tutorial.
USoft is likely to send set operators to the RDBMS for evaluation. This may affect performance. Use Profiler in USoft Benchmark to find out whether evaluation is client-side or RDBMS-side, and to measure the performance penalty.
|
SQL Command
|
Logical View SQL
|
Constraint SQL, main statement
|
Constraint SQL, subqueries
|
UNION
|
Yes
|
No
|
No
|
Yes
|
UNION ALL
|
Yes
|
Yes
|
No
|
Yes
|
MINUS
|
Yes
|
No
|
No
|
Yes
|
INTERSECT
|
Yes
|
No
|
No
|
Yes
|
The UNION operator combines the outcome of 2 distinct SELECT statements into a single result set. Duplicate rows are discarded (this is the difference with UNION ALL). The two queries must yield results that exhibit the same structure. ORDER BY is allowed only as a modifier of the entire construct, ie., at the very end, not at the end of each SELECT statement.
With UNION, the result is equivalent independently of which of the SELECT statements appears first.
Syntax
SELECT-statement
UNION
SELECT-statement
|
|
The UNION ALL operator combines the outcome of 2 distinct SELECT statements into a single result set. Duplicate rows are preserved: the result set is not a true set, but a list of items that could contain identical elements (this is the difference with UNION). The two queries must yield results that exhibit the same structure. ORDER BY is allowed only as a modifier of the entire construct, ie., at the very end, not at the end of each SELECT statement.
With UNION ALL, the result is equivalent independently of which of the SELECT statements appears first.
Syntax
SELECT-statement
UNION ALL
SELECT-statement
|
|
The MINUS operator removes, from the outcome of the SELECT statement appearing before it, any rows that are also in the outcome of a second SELECT statement appearing after it. The two queries must yield results that exhibit the same structure. ORDER BY is allowed only as a modifier of the entire construct, ie., at the very end, not at the end of each SELECT statement.
With MINUS, the result is different depending on whether the first or the second SELECT statement appears first.
Syntax
SELECT-statement-1
MINUS
SELECT-statement-2
|
|
The INTERSECT operator removes, from the outcomes or result sets of 2 distinct SELECT statements, any rows that are not in both result sets (ie., any rows that are in one result set only). The two queries must yield results that exhibit the same structure. ORDER BY is allowed only as a modifier of the entire construct, ie., at the very end, not at the end of each SELECT statement.
With INTERSECT, the result is equivalent independently of which of the SELECT statements appears first.
Syntax
SELECT-statement
INTERSECT
SELECT-statement
|
|
See also
Queries and subqueries
Join constructs
|
