Databricks SQL Tutorial: Combine Result Sets with UNION and UNION ALL

This tutorial guides you through combining result sets in Databricks SQL using the `UNION` and `UNION ALL` statements. We’ll progress through increasingly complex examples to solidify your understanding.

Script 1: Basic UNION

The first script demonstrates the fundamental use of `UNION` to combine the results of two `SELECT` statements. `UNION` automatically removes duplicate rows.

<ul>
<li>CREATE TABLE employees (employee_id INT, employee_name STRING, department STRING);</li>
<li>INSERT INTO employees VALUES (1, 'Alice', 'Sales');</li>
<li>INSERT INTO employees VALUES (2, 'Bob', 'Marketing');</li>
<li>INSERT INTO employees VALUES (3, 'Charlie', 'Sales');</li>
</ul>
-- Create a second table with slightly different data.
CREATE TABLE interns (employee_id INT, employee_name STRING, department STRING);
INSERT INTO interns VALUES (4, 'David', 'Sales');
INSERT INTO interns VALUES (5, 'Eve', 'Marketing');
-- Union the two tables.
SELECT employee_id, employee_name, department FROM employees
UNION
SELECT employee_id, employee_name, department FROM interns;
</pre>

This script creates two sample tables, `employees` and `interns`. It then uses `UNION` to combine the rows from both tables into a single result set. The final `SELECT` statement verifies that the combined result set contains all rows from both input tables, and that there are no duplicates.

The expected output for this script is:

SELECT employee_id, employee_name, department FROM employees
UNION
SELECT employee_id, employee_name, department FROM interns;

Output:

+------------------+---------------+---------+
|employee_id|employee_name|department|
+------------------+---------------+---------+
|            1|      Alice|     Sales|
|            2|        Bob|  Marketing|
|            3|    Charlie|     Sales|
|            4|      David|     Sales|
|            5|        Eve|  Marketing|
+------------------+---------------+---------+

Script 2: Using UNION ALL
This script demonstrates `UNION ALL` which combines the results of two `SELECT` statements without removing duplicate rows. It is generally faster than `UNION` because it doesn't need to perform a duplicate elimination step.
<ul>
<li>CREATE TABLE sales_transactions (transaction_id INT, product_name STRING, sale_amount DECIMAL(10,2));</li>
<li>INSERT INTO sales_transactions VALUES (101, 'Laptop', 1200.00);</li>
<li>INSERT INTO sales_transactions VALUES (102, 'Mouse', 25.00);</li>
<li>INSERT INTO sales_transactions VALUES (103, 'Keyboard', 75.00);</li>
<li>INSERT INTO sales_transactions VALUES (101, 'Laptop', 1200.00);</li>
<li>INSERT INTO sales_transactions VALUES (104, 'Monitor', 300.00);</li>
</ul>
-- Create a second table with more transactions.
CREATE TABLE returns (transaction_id INT, product_name STRING, refund_amount DECIMAL(10,2));
INSERT INTO returns VALUES (101, 'Laptop', 100.00);
INSERT INTO returns VALUES (102, 'Mouse', 20.00);
-- Union all the tables.
SELECT transaction_id, product_name, sale_amount FROM sales_transactions
UNION ALL
SELECT transaction_id, product_name, refund_amount FROM returns;
</pre>

This script demonstrates `UNION ALL`. It creates two sample tables, `sales_transactions` and `returns`, containing transaction data. The `UNION ALL` statement combines the data from both tables. Note that the `Laptop` transaction appears twice due to the duplicate entry in the `returns` table. This is the behavior of `UNION ALL`. The final `SELECT` statement validates the combined result.

The expected output for this script is:

SELECT transaction_id, product_name, sale_amount FROM sales_transactions
UNION ALL
SELECT transaction_id, product_name, refund_amount FROM returns;

Output:

+--------------+---------------+------------+
|transaction_id|product_name  |sale_amount |
+--------------+---------------+------------+
|            1|      Laptop|    1200.00 |
|            2|        Mouse|       25.00|
|            3|    Keyboard|       75.00|
|            4|      Monitor|       300.00|
|            1|      Laptop|    100.00 |
|            2|        Mouse|       20.00|
+--------------+---------------+------------+

Script 3: Combining Multiple Result Sets with UNION ALL
This script combines three tables using `UNION ALL`. This demonstrates combining result sets with more than two tables. 
<ul>
<li>CREATE TABLE products (product_id INT, product_name STRING, category STRING);</li>
<li>INSERT INTO products VALUES (1, 'Laptop', 'Electronics');</li>
<li>INSERT INTO products VALUES (2, 'Mouse', 'Electronics');</li>
<li>INSERT INTO products VALUES (3, 'Keyboard', 'Electronics');</li>
<li>INSERT INTO products VALUES (4, 'T-Shirt', 'Clothing');</li>
<li>INSERT INTO products VALUES (5, 'Jeans', 'Clothing');</li>
</ul>
-- Create a second table with different products.
CREATE TABLE accessories (product_id INT, product_name STRING, category STRING);
INSERT INTO accessories VALUES (6, 'Headphones', 'Electronics');
INSERT INTO accessories VALUES (7, 'Belt', 'Clothing');
-- Union all the tables.
SELECT product_id, product_name, category FROM products
UNION ALL
SELECT product_id, product_name, category FROM accessories;
</pre>

This script combines the `products` and `accessories` tables using `UNION ALL`. It demonstrates combining multiple result sets. The expected output includes all products from both tables, and there are no duplicates due to `UNION ALL`.

The expected output for this script is:

SELECT product_id, product_name, category FROM products
UNION ALL
SELECT product_id, product_name, category FROM accessories;

Output:

+------------+---------------+---------+
|product_id|product_name  |category |
+------------+---------------+---------+
|          1|      Laptop|Electronics|
|          2|        Mouse|Electronics|
|          3|    Keyboard|Electronics|
|          4|       T-Shirt|   Clothing|
|          5|       Jeans|   Clothing|
|          6|  Headphones|Electronics|
|          7|       Belt|   Clothing|
+------------+---------------+---------+

Output:

SELECT product_id, product_name, category FROM products
UNION ALL
SELECT product_id, product_name, category FROM accessories;

Output:

+--------------+---------------+---------+
|product_id|product_name  |category |
+--------------+---------------+---------+
|            1|      Laptop|Electronics|
|            2|        Mouse|Electronics|
|            3|    Keyboard|Electronics|
|            4|       T-Shirt|   Clothing|
|            5|       Jeans|   Clothing|
|            6|  Headphones|Electronics|
|            7|       Belt|   Clothing|
+--------------+---------------+---------+