Summary  
This chapter explains how to use the SQL UNION clause to merge the results of multiple SELECT queries with matching columns and data types, remove duplicates by default (or retain them with UNION ALL), and further manipulate the combined result using ORDER BY or subqueries.  

General domain of usage  
Relational database querying

You've caught the attention of a client who needs SQL queries. Before diving into `JOINs`, you'll learn about the `UNION` clause, which is essential for combining multiple tables.

`UNION` in SQL is used to combine the results of two or more `SELECT` queries into one resulting set. It allows you to merge rows from multiple queries into a single result set of data.

Definition

Here are some important things to know about using `UNION`:

1. **Columns Count and Order:** all queries combined with `UNION` must have the **same number of columns** in the **same order**;

2. **Data Types:** the columns in each query must have **compatible data types**;

3. **Unique Rows:** by default, `UNION` removes duplicate rows. Use `UNION ALL` if you want to keep duplicates.

For a better understanding of **how to correctly combine tables** using the `UNION` clause, An **additional table** that contains information about `contractors` has been introduced. 

There's not much information here, just the `first_name`, `last_name`, and `email`. 

You can see that this table has similarities with the `employees` table. Using the `UNION` clause, you can **combine these two tables** to, for example, see a list of all names and surnames of **employees and contractors** involved in the company. 

To do this, you'll use the `UNION` clause:

(SELECT employee_id as id, first_name, last_name
FROM employees)

UNION 

(SELECT contractor_id as id, first_name, last_name
FROM contractors)
ORDER BY id

There are **two queries** that each return **three columns** with the same data types. The goal is to see the `Id`, `first_name`, and `last_name` of everyone in the company. The `Id` column was also renamed so both queries have the **same column names**.

`UNION` **combines the results** of these queries, removing duplicates (though there are none here).

Finally, the results are sorted by `Id` using `ORDER BY`.

The results are sorted by `Id`, which is a **common column** in both tables.

Note

After using `UNION`, you get a "**single large query**" that you can further manipulate with clauses like `ORDER BY`.

You can't directly use `WHERE` or `GROUP BY` with tables combined using `UNION`. To apply these clauses, you need to use a **subquery** in the `FROM` section. Here's an example of how to do it:

SELECT id, first_name, last_name
FROM (
  SELECT employee_id AS id, first_name, last_name
  FROM employees
  UNION 
  SELECT contractor_id AS id, first_name, last_name
  FROM contractors
  ) AS combined
WHERE first_name = 'Jane'

Using a subquery in the `FROM` section gives us more flexibility! It might seem tricky at first, but mastering this will make writing complex queries much easier.

What requirements must result sets meet to be combined using `UNION`?

How does the `UNION` clause treat duplicate rows by default?

This course is perfect for those who already have a basic understanding of SQL and want to delve into more advanced concepts to craft more powerful queries. Throughout the course, you will become familiar with data grouping and filtering grouped data. You will also learn how to work with multiple tables simultaneously, including how to combine them. Additionally, you will explore different types of table joins and how to apply them in practice.

In this section, you will learn how to group and manage data effectively. We will introduce the GROUP BY operator for grouping data. Additionally, you will learn how to filter grouped data using the HAVING operator.

You will learn about nested subqueries, how to create them, and how to use them effectively.

Here, you will learn how to combine multiple tables, allowing you to work with a unified table and simplify query creation.

Learn the fundamentals of Data Definition Language (DDL) and Data Manipulation Language (DML) in SQL, including how to create, modify, and delete database objects, as well as how to insert, update, and delete data within tables.

UNION Clause

1. What requirements must result sets meet to be combined using `UNION`?

2. How does the `UNION` clause treat duplicate rows by default?

UNION Clause

1. What requirements must result sets meet to be combined using UNION?

2. How does the UNION clause treat duplicate rows by default?

1. What requirements must result sets meet to be combined using `UNION`?

2. How does the `UNION` clause treat duplicate rows by default?