​
UNION

​
Overview

The UNION combines the result sets of 2 or more select statements, removing duplicate rows between the tables.

​
Syntax

Below is the syntax of the UNION:

SELECT value1, value2, ... value_n
FROM table1
UNION
SELECT value1, value2, ... value_n
FROM table2;

The parameters from the syntax are explained below:

  • value1, value2, ... value_n: The columns you wish to retrieve. You can also retrieve all the values using the SELECT * FROM query.

  • table1, table2: The tables that you wish to retrieve records from.

Things to consider:
1. The data types of corresponding columns in the SELECT queries must be compatible.
2. The order of columns is flexible as long as the columns in consecutive places are pairwise compatible. For example, you can do SELECT col1, col2 FROM table1 UNION SELECT col2, col1 FROM table2.

​
Example

Let’s consider an example of the UNION. Assume we have a table called employees and another table called contractors. We want to retrieve a combined list of names from both tables, excluding duplicates:

CREATE TABLE employees (
    emp_id INT,
    emp_name TEXT
);

CREATE TABLE contractors (
    contractor_id INT,
    contractor_name TEXT
);

INSERT INTO employees VALUES
(1, 'John'),
(2, 'Alice'),
(3, 'Bob');

INSERT INTO contractors VALUES
(101, 'Alice'),
(102, 'Eve'),
(103, 'Tom');

Verifying inserted values by using the SELECT statement:

SELECT * FROM employees;
SELECT * FROM contractors;

emp_id | emp_name
--------+----------
      1 | John
      2 | Alice
      3 | Bob

 contractor_id | contractor_name
---------------+-----------------
           101 | Alice
           102 | Eve
           103 | Tom

Let’s combine the values from the tables:

SELECT emp_name FROM employees
UNION
SELECT contractor_name FROM contractors;

You will get the values of both tables, and there won’t be any duplicate values.

emp_name
----------
 Alice
 Bob
 Eve
 John
 Tom

The diagram below shows that the duplicate name “Alice” is represented only once in the output, fulfilling the requirement to avoid duplicate entries.

​
UNION ALL

​
Overview

The UNION ALL combines the result sets of 2 or more select statements, returning all rows from the query and not removing duplicate rows between the tables.

​
Syntax

Below is the syntax of the UNION ALL:

SELECT value1, value2, ... value_n
FROM tables
UNION ALL
SELECT value1, value2, ... value_n
FROM tables;

The parameters from the syntax are explained below:

  • value1, value2, ... value_n: The columns you wish to retrieve. You can also retrieve all the values using the SELECT * FROM query.

  • table1, table2: The tables that you wish to retrieve records from.

Things to consider:
1. The data types of corresponding columns in the SELECT queries must be compatible.
2. The order of columns is flexible as long as the columns in consecutive places are pairwise compatible.

​
Example

Suppose you have two separate tables, sales_2022 and sales_2023, containing sales data for different years. You want to combine the sales data from both tables to get a complete list of sales transactions without removing duplicates.

CREATE TABLE sales_2022 (
    transaction_id INT,
    product_name TEXT,
    sale_amount INT
);

CREATE TABLE sales_2023 (
    transaction_id INT,
    product_name TEXT,
    sale_amount INT
);

INSERT INTO sales_2022 VALUES
(1, 'Product A', 1000),
(2, 'Product B', 500),
(3, 'Product C', 750);

INSERT INTO sales_2023 VALUES
(4, 'Product A', 1200),
(5, 'Product D', 800),
(6, 'Product E', 950);

Verifying inserted values by using the SELECT statement:

SELECT * FROM sales_2022;
SELECT * FROM sales_2023;

transaction_id | product_name | sale_amount
----------------+--------------+-------------
              1 | Product A    |        1000
              2 | Product B    |         500
              3 | Product C    |         750

 transaction_id | product_name | sale_amount
----------------+--------------+-------------
              4 | Product A    |        1200
              5 | Product D    |         800
              6 | Product E    |         950

Let’s combine all values from the tables by using the UNION ALL: 

SELECT product_name, sale_amount FROM sales_2022 UNION ALL SELECT product_name, sale_amount FROM sales_2023;

In this case, it will display all the values of the first table followed by all the contents of the second table.

product_name | sale_amount
--------------+-------------
 Product A    |        1000
 Product B    |         500
 Product C    |         750
 Product A    |        1200
 Product D    |         800
 Product E    |         950

The diagram illustrates that with the UNION ALL, all values are displayed, including the duplicate ones.

INTERSECTOverview