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?
  1. SELECT <column_list> t [INTO]
  2. [FROM] [WHERE]
  3. [GROUP BY] [HAVING]
  4. [UNION [ALL]
  5. SELECT <column_list>
  6. [FROM] [WHERE]
  7. [GROUP BY] [HAVING] ...]
  8. [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?
  1. SELECT prod_code, prod_name
  2. FROM product
  3. UNION
  4. SELECT prod_code, prod_name
  5. 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?
  1. SELECT prod_code, prod_name, com_name
  2. FROM product
  3. UNION ALL
  4. SELECT prod_code, prod_name, com_name
  5. 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?
  1. SELECT prod_code, prod_name, com_name
  2. FROM product
  3. WHERE life> 6
  4. UNION ALL
  5. SELECT prod_code, prod_name, com_name
  6. FROM purchase
  7. 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?
  1. SELECT prod_code, prod_name, com_name
  2. FROM purchase
  3. WHERE pur_qty> 6
  4. UNION ALL
  5. SELECT prod_code, prod_name, com_name
  6. FROM purchase
  7. 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?
  1. SELECT prod_code, prod_name, life
  2. FROM product
  3. WHERE life> 6
  4. UNION
  5. SELECT prod_code, prod_name, pur_qty
  6. FROM purchase
  7. 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?
  1. SELECT product.prod_code, product.prod_name,
  2. purchase.pur_qty, purchase.pur_amount
  3. FROM product
  4. INNER JOIN purchase
  5. ON product.prod_code = purchase.prod_code
  6. UNION
  7. SELECT product.prod_code, product.prod_name,
  8. purchase.pur_qty, purchase.pur_amount
  9. FROM product
  10. INNER JOIN purchase
  11. 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?
  1. select field1
  2. from table1
  3. UNION
  4. select field1
  5. from table2;

Output

Explaining SQL UNION using the Venn Diagrams as an Example

UNION ALL Example:

view plainprint?
  1. select field1
  2. from table1
  3. UNION ALL
  4. select field1
  5. 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

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