UNION and UNION ALL in MySQL Workbench

• In MySQL, both `UNION` and `UNION ALL` are used to combine the result sets of two or more SELECT queries into a single result set. However, there's a key difference between them: 

• UNION: The `UNION` operator combines the result sets of multiple queries and eliminates duplicate rows from the final result set. If there are rows that are duplicated across the result sets, only one instance of the duplicated row will appear in the final result.

• UNION ALL: The `UNION ALL` operator also combines the result sets of multiple queries, but it includes all rows from the result sets, even if they are duplicates. This means that if there are any duplicated rows in the individual result sets, they will also be duplicated in the final result.

Here's a more detailed explanation along with a real-life example:

• Suppose you have two tables: `customers` and `suppliers`. You want to retrieve a list of distinct names from both tables, and also include any duplicates if they exist.

• Table: customers

    -- Create the 'customers' table

    CREATE TABLE customers (

        id INT PRIMARY KEY,

        name VARCHAR(50)

    );

    -- Insert data into the 'customers' table

    INSERT INTO customers (id, name)

    VALUES

        (1, 'Alice'),

        (2, 'Bob'),

        (3, 'Charlie'),

        (4, 'Alice');

• Table: suppliers

    -- Create the 'suppliers' table

    CREATE TABLE suppliers (

        id INT PRIMARY KEY,

        name VARCHAR(50)

    );

    -- Insert data into the 'suppliers' table

    INSERT INTO suppliers (id, name)

    VALUES

        (1, 'David'),

        (2, 'Alice'),

        (3, 'Eve'),

        (4, 'Frank');

• Using UNION:

    SELECT name FROM customers

    UNION

    SELECT name FROM suppliers;

• Result:

• Notice that duplicates like "Alice" are removed in the result.

• Using UNION ALL:

    SELECT name FROM customers

    UNION ALL

    SELECT name FROM suppliers;

• Result:

• In this case, all rows from both tables are included in the result, even the duplicates.

• In summary, use `UNION` when you want to eliminate duplicates, and use `UNION ALL` when you want to include all rows, even duplicates, in the final result set.