{"id":1705,"date":"2020-11-09T07:00:58","date_gmt":"2020-11-09T04:00:58","guid":{"rendered":"https:\/\/www.dataplatform.gr\/?p=1705"},"modified":"2024-07-01T17:52:27","modified_gmt":"2024-07-01T14:52:27","slug":"ti-einai-ta-set-operators-union-kai-ti-diafora-echoyn-ap","status":"publish","type":"post","link":"https:\/\/www.dataplatform.gr\/en\/ti-einai-ta-set-operators-union-kai-ti-diafora-echoyn-ap\/","title":{"rendered":"What are set operators (Union) and how they differ from Joins in SQL"},"content":{"rendered":"

In this article we will analyze the use of set operators, the types that exist and the differences between them and SQL Joins.<\/p>\n\n\n\n

In her world SQL<\/strong> (Structured Query Language) many times we will want to combine two different data sets into a new one. This is achieved by using set operators<\/strong> \/ union operator<\/strong>.<\/p>\n\n\n\n

How is it different with SQL Joins?<\/h2>\n\n\n\n

Sta SQL Joins<\/strong> we compare columns from two different tables by making records consisting of the columns of those two tables.<\/p>\n\n\n\n

Sta Union operators<\/strong> we join the records (lines) of two different result sets (queries) into a single one.<\/p>\n\n\n\n

Set operators<\/h2>\n\n\n\n

There are four kinds of set operators: Union all, Union, Except, Intersect<\/strong><\/p>\n\n\n\n

Depending on which one we use will return a different result.<\/p>\n\n\n\n

Let's explain them one by one with examples.<\/p>\n\n\n\n

To begin with, we will make two tables with data so that it is easier to understand the difference between each type:<\/p>\n\n\n\n

create table pelatis (\n  onoma_pelati varchar(50));\n\ncreate table pwlitis (\n  onoma_pwliti varchar(50));\n  \ninsert into pelatis values\n('Stratos'),\n('Nikos'),\n('Iasonas');\n\ninsert into pwlitis values\n('Stratos'),\n('Giorgos'),\n('Stefanos');<\/pre>\n\n\n\n
select * from pelatis;\nselect * from pwlitis;<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Union All<\/h2>\n\n\n\n
In the Union All operator, we combine the results from several queries into a single one that also contains all records that are entirely duplicates.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
select * from pelatis\nunion all\nselect * from pwlitis;<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
We see that \u201cStratos\u201d appears twice as it exists in both sets of results.<\/p>\n\n\n\n
Union<\/h2>\n\n\n\n
In the Union <\/strong>operator as opposed to Union All<\/strong> when we combine the results from several queries into a single one, not <\/strong>contains as many records as are entirely duplicates.<\/p>\n\n\n\n
select * from pelatis\nunion\nselect * from pwlitis;<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
We see this time that \u201cStratos\u201d only exists once, even though it exists in both sets of results.<\/p>\n\n\n\n
Except<\/h2>\n\n\n\n
In the Except<\/strong> operator displays the first set of results after the results of the second have been removed.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
select * from pelatis\nexcept\nselect * from pwlitis;<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Does not appear "Stratos"<\/em> as it was present in the second set of results.<\/p>\n\n\n\n
Intersect<\/h2>\n\n\n\n
The Intersect operator only displays those records that are the same in both result sets.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
select * from pelatis\nintersect\nselect * from pwlitis;<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
In this case only the registration "Stratos"<\/em> is common to both sets of results.<\/p>\n\n\n\n
Normal uses:<\/h2>\n\n\n\n