Use the panes of the SQL builder with the Outline view to build
a FULLSELECT statement.
A FULLSELECT statement combines results from subqueries and sets
of values to form a results table.
- SQL Source pane
- Use this pane to review the FULLSELECT statement
as you build the statement in the Tables pane, the Design pane, and the subqueries. You
can also type changes directly in this pane. If you change the statement in
this pane, you can parse the statement by moving the focus out of the pane
or by saving the statement. If the parser considers the statement syntax valid,
the Tables and Design pane are synchronized to reflect the updated statement.
- Tables pane
- Use this pane to select the operators to use with
the elements in the FULLSELECT statement to return the results that you want.
- Statement
- Indicates the element in the FULLSELECT statement: FULLSELECT statement,
SELECT statement, or VALUES clause.
- Operator
- Select the operator to use between the element
that is in this row and the element that is in the next row of this table.
- UNION
- The results table that is derived by combining two other results tables
(R1 and R2). The result is the set of all the rows in either R1 or R2, with
the duplicate rows eliminated.
- UNION ALL
- The results table that is derived by combining two other results tables
(R1 and R2). The result consists of all the rows in R1 and R2.
- INTERSECT
- The results table that is derived by combining two other results tables
(R1 and R2). The result consists of all the rows that are in both R1 and R2,
with the duplicate rows eliminated.
- INTERSECT ALL
- The results table that is derived by combining two other results tables
(R1 and R2). The result consists of all the rows that are in both R1 and R2.
- EXCEPT
- The results table that is derived by combining two other results tables
(R1 and R2). The result consists of all the rows that are only in R1, with
duplicate rows in the result of this operation eliminated.
- EXCEPT ALL
- The results table that is derived by combining two other results tables
(R1 and R2). The result consists of all the rows that do not have a corresponding
row in R2, where duplicate rows are significant.
- Design pane
- When the Column, Sort
Type, and Sort Order columns are shown,
use the columns in this pane to create an ORDER BY clause in the FULLSELECT
statement.
- Column
- Select the columns to include in the ORDER BY
clause. The columns in the list are the columns that you specified for
the sub-SELECT statements.
- Sort Type
- Select whether to sort the results for the corresponding
column in ascending, descending, or default order. The Default option
indicates that sorting is required, but no SQL source should be shown for
the sort type. The default sort type for your database product is used.
- Sort Order
- Select the order in which to sort the results.
- When the Values Row Item column
is shown, use this pane to define expressions that specify the values for
each column of a row in the results table of a FULLSELECT statement.You
can type a value or expression in a cell, or click Build Expression and
use the Expression Builder wizard to create an expression.