Объяснение работы SQL UNION на примере диаграмм Венна

Привет, сегодня поговорим про объяснение работы sql union на е диаграмм венна, обещаю рассказать все что знаю. Для того чтобы лучше понимать что такое объяснение работы sql union на е диаграмм венна , настоятельно рекомендую прочитать все из категории Базы данных, знаний и хранилища данных. Big data, СУБД и SQL и noSQL.

The SQL UNION operator combines the results of two or more queries and makes a result set which includes fetched rows from the participating queries in the UNION.

Table of contents :

• Basic Rules
• Difference between SQL JOIN and UNION
• SQL: UNION ALL
• SQL: UNION ALL using where
• SQL: UNION a table to itself
• SQL: UNION with different column names
• SQL: UNION with Inner Join
• SQL: Union vs Union All

Basic rules for combining two or more queries using UNION

Basic rules for combining two or more queries using UNION :

1.) number of columns and order of columns of all queries must be same.

2.) the data types of the columns on involving table in each query must be same or compatible.

3.) Usually returned column names are taken from the first query.

By default the UNION behalves like UNION [DISTINCT] , i.e. eliminated the duplicate rows; however, using ALL keyword with UNION returns all rows, including duplicates.

Difference between SQL JOIN and UNION

1.) The columns of joining tables may be different in JOIN but in UNION the number of columns and order of columns of all queries must be same.

2.) The UNION puts rows from queries after each other( puts vertically ) but JOIN puts the column from queries after each other (puts horizontally), i.e. it makes a cartesian product.

Syntax

The queries are all executed independently but their output is merged.

In the following example no clause have been added with UNION, so, by default UNION is acting as UNION [DISTINCT] and only the unique rows are available in the result set.

Sample table : product

Sample table : purchase

Output

Pictorial Representation

Go Top

SQL UNION ALL

In the following example the optional clause ALL have been added with UNION for which, all the rows from each query have been available in the result set. Here in the above output the marking rows are non-unique but it has been displayed. If ignored ALL clause, the marking rows would have come once.

Output

SQL UNION ALL using where

In the following example the two queries have been set using two different criterias including WHERE clause. Об этом говорит сайт https://intellect.icu . So all the retrieve rows (including duplicates) have displayed in the result set. Here in this example the marking rows are identical, but it has been displayed for the ALL clause along with UNION. If ignored ALL clause the marking rows would have come once.

Output

Go Top

SQL UNION a table to itself

In the following example the two queries have been set using two different criterias for a same table. So all the retrieved rows ( including duplicates ) have displayed. Here in this example the marking rows are identical, but it has been displayed for the ALL clause along with UNION.

SQL UNION with different column names

In the following example the two queries have been set using two different criterias and different columns. The different columns in two statements are 'life' and 'pur_qty'. But as the data type are same for both the columns so, result have displayed. Usually returned column names are taken from the first query.

Output

Go Top

SQL UNION with Inner Join

In the following example the union made by two queries. The queries are two inner join statement. In the first query the join take place between two tables where the prod_code of both tables are same and in the 2nd query the join take place between two tables where the prod_name of both tables are same.

Output

SQL: Union vs Union All

The basic difference between UNION and UNION ALL is, UNION removes duplicate records but UNION ALL does not. Let apply these two commands on two tables table1 and table2.

Rows in table1 :

Rows in table2 :

UNION Example (Removes all duplicate records) :

Output

UNION ALL Example :

Output

Outputs of the said SQL statement shown here is taken by using Oracle Database 10g Express Edition.

 

 

Union all is also an SQL command which is used to put together the selected values from two different tables. Unlike the Union command, the Union all does not filter the values to be returned by it. Union all, as its name suggests, returns all the values specific to the query, inclusive of the values that have been duplicated as well. However, Union all works fairly faster than the Union command. This is because there is no onus on the Union all command to eliminate duplication and present distinct values. So it works faster and displays all results as per the query.

 

Therefore, the use of Union all is recommended only in a condition where the user is confident about the displayed result not carrying any overlapping values. Otherwise, the Union command is the best option to perform an integration of values. It is a bit slower, but can be counted upon to provide an accurate result.

 

Comparison between Union and Union All:

 

	Union	Union All
Return of value	The union command returns values that are distinct from one another.	Union all displays all values specific to the query, inclusive of those that are being duplicated.
Elimination of duplicity	Union command always discards the duplicate values and presents the separate ones.	Union all doesn’t eliminate duplicate values.
Speed	Union is a bit slower as it refines the values to be returned.	Union all is comparatively faster as it just has to present all the values, regardless of clone values.
Recommended when	The values need to be sorted and united.	The values need to be united, but don’t need to be sorted.

На этом все! Теперь вы знаете все про объяснение работы sql union на е диаграмм венна, Помните, что это теперь будет проще использовать на практике. Надеюсь, что теперь ты понял что такое объяснение работы sql union на е диаграмм венна и для чего все это нужно, а если не понял, или есть замечания, то не стесняйся, пиши или спрашивай в комментариях, с удовольствием отвечу. Для того чтобы глубже понять настоятельно рекомендую изучить всю информацию из категории Базы данных, знаний и хранилища данных. Big data, СУБД и SQL и noSQL

Из статьи мы узнали кратко, но содержательно про объяснение работы sql union на е диаграмм венна