The results of two queries can be combined using the set operations UNION
and INTERSECT
.
UNION
and UNION ALL
UNION ALL
operator is as follows:
points_scored_current_week
, that consists of these columns: id
, first_half
, and second_half
.
id | first_half | second_half |
---|---|---|
1 | 10 | 20 |
points_scored_last_week
, that consists of these columns: id
, first_half
, and second_half
.
id | first_half | second_half |
---|---|---|
1 | 10 | 20 |
INTERSECT
INTERSECT
operator combines the result sets of 2 or more SELECT
statements and returns only the rows that are common to all the SELECT
statements. It removes duplicate rows from the final result set.
Each SELECT
statement within the INTERSECT
operator must have the same number of fields in the result sets with similar data types.
The syntax for the INTERSECT
operator is as follows:
FROM
clause.
WHERE conditions are optional. These conditions must be met for the records to be selected.
Suppose that we have a table,points_scored_current_week
, that consists of these columns: id
, first_half
, and second_half
.
id | first_half | second_half |
---|---|---|
1 | 10 | 20 |
points_scored_last_week
, that consists of these columns: id
, first_half
, and second_half
.
id | first_half | second_half |
---|---|---|
1 | 10 | 20 |
INTERSECT
operator:
INTERSECT
operator returned the rows that are common to both the points_scored_current_week
and points_scored_last_week
tables. If there were no common rows, the INTERSECT
operator would return an empty set.
INTERSECT
operator is supported for streaming queries.CORRESPONDING
in set operationsUNION
, INTERSECT
, and EXCEPT
) typically require:
CORRESPONDING
keyword to match columns by name instead of order. This approach:
CORRESPONDING
gives you more flexibility when performing set operations, as it doesn’t rely on strict column ordering.
The syntax for using CORRESPONDING
is as below:
<operation>
is one of the below operations:
CORRESPONDING BY
specification. Only columns that are specified and exist on both sides will be overlaid.
Here is a simple example. First, let’s create two tables employees
and managers
, and insert some data. Then you can use the CORRESPONDING
keyword.