SQL UNION ALL: Combining Results from Multiple SELECT Statements

Welcome to our comprehensive guide on the SQL UNION ALL operator! In this lesson, we'll explore how to combine the result-set of two or more SELECT statements using UNION ALL. By the end of this tutorial, you'll have a solid understanding of this powerful tool and be ready to apply it in your own projects. Let's dive in! 🎯

What is UNION ALL?

The SQL UNION ALL operator is used to combine the result-sets of two or more SELECT statements. Unlike the UNION operator, UNION ALL includes all the rows from the original result-sets without removing duplicates.

Here's the basic syntax of the UNION ALL operator:

sql

SELECT column1, column2, ... FROM table1
UNION ALL
SELECT column1, column2, ... FROM table2
...

Why Use UNION ALL?

The UNION ALL operator is particularly useful when you need to combine data from multiple tables, queries, or subqueries, and don't want to eliminate any duplicate rows. This can be helpful in various scenarios, such as merging data from multiple sources, consolidating data from different tables, or analyzing data from various perspectives. 💡

Example 1: Combining Data from Two Tables

Let's consider two tables, authors and books, and use the UNION ALL operator to combine their data:

sql

-- Create sample data for authors and books
CREATE TABLE authors (
  id INT PRIMARY KEY,
  name VARCHAR(255)
);

CREATE TABLE books (
  id INT PRIMARY KEY,
  title VARCHAR(255),
  author_id INT,
  FOREIGN KEY (author_id) REFERENCES authors(id)
);

INSERT INTO authors VALUES (1, 'John Doe'), (2, 'Jane Smith');
INSERT INTO books VALUES (1, 'Book 1', 1), (2, 'Book 2', 1), (3, 'Book 3', 2);

-- Combine data from authors and books using UNION ALL
SELECT name FROM authors
UNION ALL
SELECT author_name FROM (
  SELECT title, GROUP_CONCAT(name SEPARATOR ', ') as author_name FROM books JOIN authors ON authors.id = books.author_id GROUP BY title
);

Output:

John Doe
Jane Smith

In this example, we first create two tables, authors and books, and insert some sample data. Then, we use the UNION ALL operator to combine the name column from the authors table with the author_name column generated from the books table using the GROUP_CONCAT function. The result is a single table containing the names of all authors and their corresponding book titles, with duplicates preserved.

Example 2: Combining Results from Multiple Subqueries

Let's take another example where we use UNION ALL to combine the results of multiple subqueries:

sql

-- Create a table for employees
CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(255),
  department VARCHAR(255)
);

-- Insert some sample data
INSERT INTO employees VALUES (1, 'John Doe', 'IT'), (2, 'Jane Smith', 'HR'), (3, 'Jim Brown', 'IT'), (4, 'Lisa Johnson', 'Finance');

-- Combine data from employees using UNION ALL and subqueries
SELECT name, department FROM employees
UNION ALL
SELECT 'Manager' AS name, 'Management' AS department
UNION ALL
SELECT CONCAT('IT Director: ', name) AS name, 'IT' AS department FROM (
  SELECT name FROM employees WHERE department = 'IT'
);

Output:

John Doe    IT
Jane Smith  HR
Jim Brown   IT
Manager    Management
IT Director: John Doe  IT
IT Director: Jim Brown IT

In this example, we first create a employees table and insert some sample data. Then, we use the UNION ALL operator to combine the name and department columns from the employees table with a subquery that returns the names of IT employees, and another subquery that returns a single row for the IT manager.

Quiz

With these examples and explanations, you now have a solid foundation for using the SQL UNION ALL operator in your projects. Happy coding, and remember: with practice, you'll become a SQL ninja! 💪🏼