UNION
SQL Support
#890
chloeminkyung
started this conversation in
Feature Requests
Replies: 1 comment 1 reply
-
I think it would be good to document how the union will work for inserts, deletes and updates and what actions are taken for each of the CDC message |
Beta Was this translation helpful? Give feedback.
1 reply
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
-
query1 UNION query2
According to how postgresql supports
UNION
:Dozer's
UNION
support functionality will follow the above requirement.Introducing above
SetOperatorType
as a placeholder for the rest of the set operators support as well.Implementation Approach
query1 UNION query2
<select-query> UNION <select-query>
Example - Single Field With Same Name
Let's look at how to use the SQL
UNION
operator that returns one field. In this simple example, the field in bothSELECT
statements will have the same name and data type.For example:
suppliers
andorders
table, it would appear once in your result set. TheUNION
operator removes duplicates. If you do not wish to remove duplicates, should try using theUNION ALL
operator. Now, let's explore this example further will some data. If you had thesuppliers
table populated with the following records:And you executed the following
UNION
statement:You would get the following results:
Example - Different Field Names
It is not necessary that the corresponding columns in each
SELECT
statement have the same name, but they do need to be the same corresponding data types.When you don't have the same column names between the
SELECT
statements, it gets a bit tricky, especially when you want to order the results of the query using theORDER BY
clause.Let's look at how to use the
UNION
operator with different column names and order the query results.For example:
In this SQL
UNION
example, since the column names are different between the twoSELECT
statements, it is more advantageous to reference the columns in theORDER BY
clause by their position in the result set. In this example, we've sorted the results bysupplier_id
/company_id
in ascending order, as denoted by theORDER BY 1
. Thesupplier_id
/company_id
fields are in position #1 in the result set.Now, let's explore this example further with data.
If you had the suppliers table populated with the following records:
And the companies table populated with the following records:
And you executed the following
UNION
statement:You would get the following results:
First, notice that the record with
supplier_id
of3000
only appears once in the result set because theUNION
query removed duplicate entries.Second, notice that the column headings in the result set are called
supplier_id
andsupplier_name
. This is because these were the column names used in the firstSELECT
statement in theUNION
.If you had wanted to, you could have aliased the columns as follows:
Now the column headings in the result will be aliased as
ID_Value
for the first column andName_Value
for the second column.Beta Was this translation helpful? Give feedback.
All reactions