Explaining SQL UNION using the Venn Diagrams as an Example

Lecture

The SQL UNION operator combines the results of the two.

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.) the number of columns must be the same.

2.) it must be the same or compatible.

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

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

Difference between SQL JOIN and UNION

1.) It can be different.

2.) The UNION puts it at each other (but it is JOIN), ie it makes a cartesian product.

Syntax

view plainprint?

SELECT <column_list> t [INTO]
[FROM] [WHERE]
[GROUP BY] [HAVING]
[UNION [ALL]
SELECT <column_list>
[FROM] [WHERE]
[GROUP BY] [HAVING] ...]
[ORDER BY]

Your queries are not executed correctly.

The following example has been given to it.

Sample table: product

Explaining SQL UNION using the Venn Diagrams as an Example

Sample table: purchase

Explaining SQL UNION using the Venn Diagrams as an Example

view plainprint?

SELECT prod_code, prod_name
FROM product
UNION
SELECT prod_code, prod_name
FROM purchase;

Output

Explaining SQL UNION using the Venn Diagrams as an Example

Pictorial Representation

Explaining SQL UNION using the Venn Diagrams as an Example

Go top

SQL UNION ALL

It has been added that it has been added. This is where the rows rows were displayed. If Ignored

view plainprint?

SELECT prod_code, prod_name, com_name
FROM product
UNION ALL
SELECT prod_code, prod_name, com_name
FROM purchase;

Output

Explaining SQL UNION using the Venn Diagrams as an Example

SQL UNION ALL using where

There are two different criterias, including WHERE clause. So all the retrieve rows (including duplicates) have displayed in the result set. It has been displayed along with UNION. If Ignored

view plainprint?

SELECT prod_code, prod_name, com_name
FROM product
WHERE life> 6
UNION ALL
SELECT prod_code, prod_name, com_name
FROM purchase
WHERE pur_qty> 10

Output

Explaining SQL UNION using the Venn Diagrams as an Example

Go top

SQL UNION a table to itself

In the following example, the two queries have been set. So all the retrieved rows (including duplicates) have displayed. It has been displayed along with UNION.

view plainprint?

SELECT prod_code, prod_name, com_name
FROM purchase
WHERE pur_qty> 6
UNION ALL
SELECT prod_code, prod_name, com_name
FROM purchase
WHERE pur_amount> 100000

Explaining SQL UNION using the Venn Diagrams as an Example

SQL UNION with different column names

In the following example, the two queries have been set using two different criteria and different columns. In different statements are 'life' and 'pur_qty'. Here is the result so that you can see the result. Usually returned column names are taken from the first query.

view plainprint?

SELECT prod_code, prod_name, life
FROM product
WHERE life> 6
UNION
SELECT prod_code, prod_name, pur_qty
FROM purchase
WHERE pur_qty <20

Output

Explaining SQL UNION using the Venn Diagrams as an Example

Go top

SQL UNION with Inner Join

In the following example. The queries are two inner join statement. In the case of the tables, it is the same.

view plainprint?

SELECT product.prod_code, product.prod_name,
purchase.pur_qty, purchase.pur_amount
FROM product
INNER JOIN purchase
ON product.prod_code = purchase.prod_code
UNION
SELECT product.prod_code, product.prod_name,
purchase.pur_qty, purchase.pur_amount
FROM product
INNER JOIN purchase
ON product.prod_name = purchase.prod_name;

Output

Explaining SQL UNION using the Venn Diagrams as an Example

SQL: Union vs Union All

UNION and UNION ALL does not. And

Rows in table1:

Explaining SQL UNION using the Venn Diagrams as an Example

Rows in table2:

Explaining SQL UNION using the Venn Diagrams as an Example

UNION Example (Removes all duplicate records):

view plainprint?

select field1
from table1
UNION
select field1
from table2;

Output

Explaining SQL UNION using the Venn Diagrams as an Example

UNION ALL Example:

view plainprint?

select field1
from table1
UNION ALL
select field1
from table2;

Output

Explaining SQL UNION using the Venn Diagrams as an Example

  SQL Database 10g Express Edition.

Explaining SQL UNION using the Venn Diagrams as an Example

This is a list of different tables. It has been chosen to Returns to the list of all values that have been duplicated as well. However, Union all works rather than the Union command. This is because there is no difference. So it works faster and displays all results.

Therefore, it’s not a problem. Otherwise, it’s possible to integrate values. It can be a bit slower.

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 that are being duplicated.
Elimination of duplicity	Discards of separate orders.	Union all doesn’t eliminate duplicate values.
Speed	Union is a bit slower.	It can be compared with all the values, regardless of clone values.
Recommended when	The values need to be sorted and united.	Need to be sorted.

Comments

To leave a comment

If you have any suggestion, idea, thanks or comment, feel free to write. We really value feedback and are glad to hear your opinion.

To reply

Comment

To confirm that you are not a bot, answer:

Name

Email(not published)

Vote

Explaining SQL UNION using the Venn Diagrams as an Example

Basic rules for combining two or more queries using UNION

Difference between SQL JOIN and UNION

Syntax

Sample table: product

Sample table: purchase

Output

Pictorial Representation

SQL UNION ALL

Output

SQL UNION ALL using where

Output

SQL UNION a table to itself

SQL UNION with different column names

Output

SQL UNION with Inner Join

Output

SQL: Union vs Union All

UNION Example (Removes all duplicate records):

Output

UNION ALL Example:

Output

Comments

To leave a comment

Databases, knowledge and data warehousing. Big data, DBMS and SQL and noSQL

Terms: Databases, knowledge and data warehousing. Big data, DBMS and SQL and noSQL