SQL UNION Opeartor

The SQL UNION operator is used to fetch or retrieve the result-set of two or more SQL SELECT statements.

Notice that each SQL SELECT query within the UNION operator must have the same number of fields.
The fields must also have similar datatypes. Also, the fields in each SQL SELECT query must be in the same order.

The SQL UNION clause/operator is used to aggregate the results of two or more SELECT queries into a single result set that contains all of the rows from all of the SELECT statements in the union. It eliminates duplicate rows between SELECT commands.

Each SELECT statement must include the UNION clause to use it.

• Each UNION table must have the same number of columns or be compatible with one another.
• The number of column expressions is the same.
• The data types in the columns must be the same.
• Each table's columns must be in the same order.
• They don't have to be the same length, though.

Syntax:

The syntax for the UNION operator in SQL is:

SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];

Parameters or Arguments

• expression1, expression2, expression_n - The columns or calculations that you wish to retrieve.
• tables - The tables from which you want to get records. At least one table must be mentioned in the FROM clause.
• WHERE conditions - Optional. The criteria that must be met in order for records to be chosen.

Example 1: The SQL statement below searches the "Geeks1" and "Geeks2" tables for cities (only unique values):

SELECT City 
FROM Geeks1

UNION

SELECT City 
FROM Geeks2
ORDER BY City; 

Output:

Example 2: suppliers table populated with the following records:

supplier_id	supplier_name
1000		Microsoft
2000		Oracle
3000		Apple
4000		Samsung

And the orders table populated with the following records:

order_id	order_date	supplier_id
1	    	2015-08-01	    2000
2		    2015-08-01	    6000
3		    2015-08-02	    7000
4		    2015-08-03	    8000

And you executed the following UNION statement:

SELECT supplier_id
FROM suppliers
UNION
SELECT supplier_id
FROM orders
ORDER BY supplier_id;

Output:

As you can see in this instance, the UNION took all supplier_id values from both the suppliers and orders tables and combined them into a single result set. Although the supplier_id of 2000 exists in both the suppliers and orders tables, the UNION operator removes duplicates between the result sets, so it only occurs once.

Example 4:

Consider the following two tables.

Table 1 − CUSTOMERS Table is as follows.

Table 2 − ORDERS Table is as follows.

+-----+---------------------+-------------+--------+
|OID  | DATE                | CUSTOMER_ID | AMOUNT |
+-----+---------------------+-------------+--------+
| 102 | 2009-10-08 00:00:00 |           3 |   3000 |
| 100 | 2009-10-08 00:00:00 |           3 |   1500 |
| 101 | 2009-11-20 00:00:00 |           2 |   1560 |
| 103 | 2008-05-20 00:00:00 |           4 |   2060 |
+-----+---------------------+-------------+--------+

Now, let us join these two tables in our SELECT statement as follows :

SELECT  ID, NAME, AMOUNT, DATE
   FROM CUSTOMERS
   LEFT JOIN ORDERS
   ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
UNION
   SELECT  ID, NAME, AMOUNT, DATE
   FROM CUSTOMERS
   RIGHT JOIN ORDERS
   ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

This would produce the following result

+------+----------+--------+---------------------+
| ID   | NAME     | AMOUNT | DATE                |
+------+----------+--------+---------------------+
|    1 | Ramesh   |   NULL | NULL                |
|    2 | Khilan   |   1560 | 2009-11-20 00:00:00 |
|    3 | kaushik  |   3000 | 2009-10-08 00:00:00 |
|    3 | kaushik  |   1500 | 2009-10-08 00:00:00 |
|    4 | Chaitali |   2060 | 2008-05-20 00:00:00 |
|    5 | Hardik   |   NULL | NULL                |
|    6 | Komal    |   NULL | NULL                |
|    7 | Muffy    |   NULL | NULL		         |
+------+----------+--------+---------------------+

You can count on a union. to obtain the UNION result count.

Combine all of the table counts into a single result set.

The UNION IS EVERYTHING. A UNION does an implicit distinct, which means that any tables with the same number of rows will have duplicates deleted.

Syntax :

SELECT COUNT(*)
FROM
(
SELECT yourColumName1 from yourTableName1
UNION
SELECT yourColumName1 from yourTableName2
) anyVariableName;

Example 1: To understand the above syntax, let us create two tables with some records.

Step 1: create a table

create table union_Table1
 (
 UserId int
 );

Step 2: Insert some records in the table using insert command.

insert into union_Table1 values(1);
Query OK, 1 row affected (0.18 sec)

insert into union_Table1 values(10);
Query OK, 1 row affected (0.12 sec)

insert into union_Table1 values(20);
Query OK, 1 row affected (0.09 sec)

Step 3: Display all records from the table using select statement. The query is as follows:

select *from union_Table1;

Output:

Step 3: The query to create a second table.

create table union_Table2
(
 UserId int
);

Step 4: Insert records in the table using insert command. The query is as follows.

insert into union_Table2 values(1);
Query OK, 1 row affected (0.12 sec)

insert into union_Table2 values(30);
Query OK, 1 row affected (0.26 sec)

insert into union_Table2 values(50);
Query OK, 1 row affected (0.13 sec)

Step 5: Display all records from the table using select command. The query is as follows −

select *from union_Table2;

Output :

Step 5: If any of the records in both tables are the identical, it will only be evaluated once. This is the query to use with the union query.

select count(*) as UnionCount from
 (
 select distinct UserId from union_Table1
 union
 select distinct UserId from union_Table2
 )tbl1;

Output :

The SQL UNION ALL function joins two or more SELECT statements into one result set. Between the multiple SELECT statements, it doesn't eliminate duplicate rows (all rows are returned).

Inside the UNION ALL, each SELECT statement must return the same amount of fields with the same data types.

Syntax :

The basic syntax of the UNION ALL is as follows.

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

UNION ALL

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

Example 1: Consider the following two tables,

Table 1 − CUSTOMERS Table is as follows.

Table 1 − CUSTOMERS Table is as follows.

Table 2 − ORDERS table is as follows.

+-----+---------------------+-------------+--------+
|OID  | DATE                | CUSTOMER_ID | AMOUNT |
+-----+---------------------+-------------+--------+
| 102 | 2009-10-08 00:00:00 |           3 |   3000 |
| 100 | 2009-10-08 00:00:00 |           3 |   1500 |
| 101 | 2009-11-20 00:00:00 |           2 |   1560 |
| 103 | 2008-05-20 00:00:00 |           4 |   2060 |
+-----+---------------------+-------------+--------+

Now, let us join these two tables in our SELECT statement as follows:

SELECT  ID, NAME, AMOUNT, DATE
   FROM CUSTOMERS
   LEFT JOIN ORDERS
   ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
UNION ALL
   SELECT  ID, NAME, AMOUNT, DATE
   FROM CUSTOMERS
   RIGHT JOIN ORDERS
   ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

Output:

+------+----------+--------+---------------------+
| ID   | NAME     | AMOUNT | DATE                |
+------+----------+--------+---------------------+
|    1 | Ramesh   |   NULL | NULL                |
|    2 | Khilan   |   1560 | 2009-11-20 00:00:00 |
|    3 | kaushik  |   3000 | 2009-10-08 00:00:00 |
|    3 | kaushik  |   1500 | 2009-10-08 00:00:00 |
|    4 | Chaitali |   2060 | 2008-05-20 00:00:00 |
|    5 | Hardik   |   NULL | NULL                |
|    6 | Komal    |   NULL | NULL                |
|    7 | Muffy    |   NULL | NULL                |
|    3 | kaushik  |   3000 | 2009-10-08 00:00:00 |
|    3 | kaushik  |   1500 | 2009-10-08 00:00:00 |
|    2 | Khilan   |   1560 | 2009-11-20 00:00:00 |
|    4 | Chaitali |   2060 | 2008-05-20 00:00:00 |
+------+----------+--------+---------------------+

Example 2: The SQL Query below will return all records (including duplicates) from the Employ table and the Employees 2015 table, as well as show the output.

SELECT [ID]
      ,[FirstName]
      ,[LastName]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
FROM [Employ]
UNION ALL
SELECT [ID]
      ,[FirstName]
      ,[LastName]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
FROM [Employees 2015]

Example 3: Let's have a look at how to utilize the SQL UNION ALL operator to create a single field. The field in both SELECT statements will have the same name and data type in this simple example.

SELECT supplier_id
FROM suppliers
UNION ALL
SELECT supplier_id
FROM orders
ORDER BY supplier_id;

If the same value occurred in both the suppliers and orders tables, this SQL UNION ALL example would return the supplier id multiple times in the result set. Duplicates are not eliminated using the SQL UNION ALL operator.

Example 4: The below SQL statement finds the cities (duplicate values also) from both the “Geeks1” and the “Geeks2” table:

SELECT City 
FROM Customers

UNION ALL

SELECT City 
FROM Suppliers
ORDER BY City; 

Output:

Example 1: paging and using UNION ALL for multiple tables:

How can I create efficient paging when using UNION ALL to join numerous tables and only return a particular number of rows.

declare @startRow int
declare @PageCount int

set @startRow = 0
set @PageCount = 20

set rowcount @PageCount

select Row_Number() OVER(Order by col1) as RowNumber, col1, col2
from
(
    select col1, col2 from table1 where datetimeCol between (@dateFrom and @dateTo)
    union all
    select col1, col2 from table2 where datetimeCol between (@dateFrom and @dateTo)
    union all
    select col1, col2 from table3 where datetimeCol between (@dateFrom and @dateTo)
    union all
    select col1, col2 from table4 where datetimeCol between (@dateFrom and @dateTo)
    union all
    select col1, col2 from table5 where datetimeCol between (@dateFrom and @dateTo)
) as tmpTable
where RowNumber > @startRow

table 3, 4, & 5 have huge number of row (millions of rows) where table 1 & 2 may only have few thousand rows.

Example 2: There is no limit to how many tables can be joined. Additional UNION or UNION ALL statements must simply be lined up one after the other for this reason:

SELECT [EmpID]
  ,[Name]
  ,[LocationID]
  ,[Location]
  ,[Year]
  ,[Target]
FROM [TargetShire]

UNION ALL

SELECT [EmpID]
  ,[Name]
  ,[LocationID]
  ,[Location]
  ,[Year]
  ,[Target]
FROM [TargetCentralMiddleEarth]

UNION ALL

SELECT [EmpID]
  ,[Name]
  ,[LocationID]
  ,[Location]
  ,[Year]
  ,[Target]
FROM [TargetMoria]

MySQL allows for GROUP BYs that do not include all columns and do not use aggregate functions. When using UNION, there's no need to run GROUP BY on the contents because UNION ensures that duplicates are deleted; UNION ALL is quicker because it doesn't, and you'd need GROUP BY in that case.

Syntax:

The syntax for UNION {ALL} is as follows:

[SQL Statement 1]
UNION {ALL}
[SQL Statement 2]
[GROUP BY ...]

Example: Your query only needs to be:

SELECT a.id,
       a.time
  FROM dbo.TABLE_A a
UNION
SELECT b.id,
       b.time
  FROM dbo.TABLE_B b

MySQL has an operator called union. To filter the records, we can use ORDER BY.

In SQL Server, we must create a sub query for union query statements in order to use the Order By property with union. To use the order by clause in SQL Server with a union statement, we must first generate a sub query for union statements, and then we can use the order by clause.

If you wish to combine rows from multiple tables or several groupings of rows from a single table into a single result set, use UNION.

The ORDER BY clause must be used at the group level, not at the subquery level, if you need to sort the result from two queries grouped along with a UNION operator.

The ORDER BY clause in a subquery of a UNION operator behaves differently in SQL Server and MySQL:

If ORDER BY is used within a UNION operator's subquery, SQL Server will throw an error.

In a subquery of a UNION operator, MySQL will skip the ORDER BY clause.

Example 1: Let us now see the query to apply UNION with Order by.

select id from UnionDemo1
  union
  select id from UnionDemo2
  order by id desc;

Output:

Example 2: The ORDER BY clause with the UNION operator is displayed in the following tutorial exercise:

(SELECT * FROM fyi_links WHERE tag = 'DBA'
   ORDER BY created)
UNION
(SELECT * FROM fyi_links WHERE tag = 'DEV'
   ORDER BY created)
GO

Output:

(SELECT * FROM fyi_links WHERE tag = 'DBA')
UNION
(SELECT * FROM fyi_links WHERE tag = 'DEV')
ORDER BY created

Example 3: The sample sql server query to employ order by clause with union statements is shown below.

SELECT * FROM
(
Select Id as UserId, UserName as Name, RegisterDate From UserDetails
Union
select UserId, EmpName as Name, ModifyDate as RegisterDate From UserDetails
) smptbl
ORDER BY RegisterDate DESC

Example 4: The query to use order by clause with union statements in SQL Server is shown below.

>SELECT * FROM
(
Select Id as UserId, UserName as Name, RegisterDate From UserDetails
Union
select UserId, EmpName as Name, ModifyDate as RegisterDate From UserDetails
) smptbl
ORDER BY RegisterDate DESC

Example 1: Union constant value

SELECT   POWER(DIGIT,2)
     FROM    (SELECT 0 AS DIGIT UNION SELECT 1 UNION
              SELECT 2 UNION SELECT 3 UNION
              SELECT 4 UNION SELECT 5 UNION
              SELECT 6 UNION SELECT 7 UNION
              SELECT 8 UNION SELECT 9) AS DIGITS1
     UNION ALL
     SELECT   POWER(DIGIT,3)
     FROM    (SELECT 0 AS DIGIT UNION SELECT 1 UNION
              SELECT 2 UNION SELECT 3 UNION
              SELECT 4 UNION SELECT 5 UNION
              SELECT 6 UNION SELECT 7 UNION
              SELECT 8 UNION SELECT 9) AS DIGITS2
    ORDER BY 1;

Output:

Example 2:

union
select  'EVY', 'Everyone', ...
from  ...
where  ... and @Grouping = 'false'

That @Grouping = false still returns the row if the value is true. I've worked out because I'm using constant values - 'EVY', 'Everyone' - instead of column values. If I change to column values it works as expected.

The UNION DISTINCT operator combines two or more input query result sets. Duplicate records will be removed using Union DISTINCT.

Syntax:

Combine distinct and union

SELECT DISTINCT DistinctValue
FROM Table1
UNION
SELECT DISTINCT DistinctValue
FROM Table2

Example 1: DISTINCT to each SELECT statement separately. Because the ALL only applies to the SELECT statement it's being applied to, Fluffy would have been returned twice if we'd done that (not to the concatenated results).

Cats

+---------+-----------+
| CatId   | CatName   |
|---------+-----------|
| 1       | Meow      |
| 2       | Fluffy    |
| 3       | Scratch   |
+---------+-----------+

Dogs

+---------+-----------+
| DogId   | DogName   |
|---------+-----------|
| 1       | Fetch     |
| 2       | Fluffy    |
| 3       | Wag       |
| 1002    | Fetch     |
+---------+-----------+

Here’s an example to illustrate what I mean.

SELECT DISTINCT DogName AS PetName
FROM Dogs
UNION
SELECT DISTINCT CatName
FROM Cats;

Result:

+-----------+
| PetName   |
|-----------|
| Fetch     |
| Fluffy    |
| Wag       |   
| Meow      |
| Scratch   |
+-----------+

Example 2: sql union distinct

SELECT * FROM Employee_Asia UNION DISTINCT SELECT * from Employee_Europe;

To aggregate the results of many SELECT operations into a single result set, use UNION.

Syntax:

select_statement UNION [ALL | DISTINCT] select_statement UNION [ALL | DISTINCT] select_statement ...

Duplicate rows are deleted from the result by default when using UNION.

Because it also specifies duplicate-row elimination, the optional DISTINCT keyword has no impact other than the default.

Duplicate-row elimination is disabled when the ALL keyword is used, and the result includes all matching rows from all SELECT operations.

Example 1: Combine multiple SELECTs using the UNION operator:

SELECT *
 FROM database1.table1 T1
 WHERE T1.age > 12
UNION
SELECT *
 FROM database2.table1 T2
 WHERE T2.age > 12;

Example 2: SQL Union Select into multiple Statement:

This should be used within a Select Into Statement. The query below selects the result and inserts it into the Duplicate Emp table.

SELECT [EmpID]
      ,[FirstName] + ' ' +[LastName] AS Name
	  ,[Education], 
	  [YearlyIncome], [Sales]
  INTO [SQL Tutorial].[dbo].[DuplicateEmp]
  FROM [Employees 2015]
UNION
SELECT [EmpID]
      ,[FirstName] + ' ' +[LastName] AS Name
	  ,[Education], 
	  [YearlyIncome], [Sales]
  FROM [Employees 2016]
  WHERE Occupation = 'Professional'

Example 3:

SELECT name, id, cast('2001-01-01' as date) d FROM source_table_1
UNION ALL
SELECT name, id, hiredate as d FROM source_table_2

A UNION is made up of two or more SELECT queries that are separated by the phrase UNION.

Each query in a UNION must have the same columns, expressions, or aggregate functions in the same sequence.

Each select query's data type must be compatible with the relevant positions: They don't have to be the same exact type, but they must be able to be implicitly converted by SQL Server.

The name of the column chosen in the various SELECT queries.

Within a derived-table expression, a union clause is permitted.

Example 1: The illustration lists the titles of books sold at stores mentioned in the sales and sales east databases using a union clause in a subquery within a SQL-derived table:

select title_id from salesdetail
   where stor_id in
      (select stor_id from
         (select stor_id from sales
          union
          select stor_id from sales_east)
       dt_stores)

Example 2: Subqueries used to combine the tables.

SELECT all_dates.emp_date, COUNT(*)
    FROM (
       SELECT hire_date emp_date
       FROM emp
       UNION
       SELECT end_date
       FROM emp) all_dates
    GROUP BY all_dates.emp_date
    ORDER BY COUNT(*) DESC;

Example 1: Select top 10 from the union results:

SELECT TOP 10 * FROM (
    SELECT
        a_object_ID as [id], 
        a_object_name as [name],
        'A_object' as [Type]
    FROM [database].[dbo].[table_a]
    WHERE a_object_name LIKE @Search + '%'

    UNION ALL

    SELECT
        b_object_ID as [id], 
        b_object_name as [name],
        'B_object' as [Type]
    FROM [database].[dbo].[table_b]
    WHERE b_object_name LIKE @Search + '%'
) u
ORDER BY u.[name]

Example 2: If more than one item is selected, a UNION line is added to the query programmatically for each item. As always, the ORDER BY is appended at the end. With all four elements checked as an instance:

SELECT TOP(10) * from dbo.Eventos WHERE Tipo LIKE '%OS%' AND Distrito LIKE '%' 
UNION ALL SELECT TOP(10) * from dbo.Eventos WHERE Tipo LIKE '%Rad%' AND Distrito LIKE '%' 
UNION ALL SELECT TOP(10) * from dbo.Eventos WHERE Tipo LIKE '%Aci%' AND Distrito LIKE '%'
UNION ALL SELECT TOP(10) * from dbo.Eventos WHERE Tipo LIKE '%Out%' AND Distrito LIKE '%' 
ORDER BY Data DESC

However, for each WHERE clause, this returns the OLDEST 10 results, sorted by Data DESC.

Main Article :- Sql difference between JOIN and UNION

While both join and union integrate data from multiple tables into a single result, their syntax, format, and structure are somewhat different.

JOIN joins data when at least one column in both tables has the same attribute, whereas UNION joins data when both tables have the same number of columns and the columns are compatible.

Joins Combine Columns

The result comprises columns from both tables A and B in each row. When columns from one table match columns from another, rows are formed. The join condition refers to this matching.

Joins are ideal for seeking up values and incorporating them into results because of this. This is frequently the outcome of denormalizing (reversing normalizing), and it entails looking for column values in one table using the foreign key in another.

Compare and contrast the preceding illustration with a union. In a union, each row in the result comes from one of two tables. In a union, rows are merged rather than columns to get results.

Unions Combine Rows

When you have two results whose rows you wish to combine into one, you'll want to utilize a union. For instance, suppose you have two tables: Teachers and Students. You want to construct a master list of names and birthdays that can be sorted by date.

To accomplish this, utilize a union to merge the rows into a single result before sorting them.

Example: Combining Data with a UNION

Let’s take a closer look at the UNION statement. In SQL, the UNION statement looks like:

SELECT columnlist
FROM   table1
UNION
SELECT columnlist
FROM   table2

Combining Data with a Join

The inner join will be discussed in this section. When you need to match rows from two tables, this is one of the most popular types of join. Rows that match are kept in the result, while those that don't are discarded.

Below is an example of a simple select statement with an INNER JOIN clause.

SELECT columnlist
FROM   maintable
       INNER JOIN 
       secondtable ON join condition

Main Article :- Sql difference between UNION and UNION ALL

UNION, EXCEPT, and INTERSECT are related operators that are used to combine the results of two queries into a Boolean combination. Duplicate records will be removed from tables returned by UNION, EXCEPT, and INTERSECT by default. To keep duplicates in the results table, select ALL.

• UNION returns all records retrieved by either query A or B when given two queries A and B.
• EXCEPT returns all records from A, but none from B.
• All records returned by both A and B are returned by INTERSECT.

UNION operation

The UNION operation combines the results of two subqueries into a single result that includes both queries' rows.

The UNION operator returns all unique rows, including duplicates, from both the left and right queries.

INTERSECT operation

The INTERSECT procedure combines the results of two searches into a single result that includes all the data that both queries have in similar. INTERSECT is a logical AND, whereas a UNION operation is a logical OR.

The INTERSECT operation returns both the left and right query's common unique rows. The duplicates have been eliminated.

EXCEPT operation

The EXCEPT/MINUS procedure finds the difference between the two queries, and the output includes only the rows from the first query.

The EXCEPT operator returns rows from the left query that aren't present in the results of the right query.

Syntax:

You can join the results from two or more SELECT statements using the common set procedures UNION, INTERSECT, and EXCEPT/MINUS to create more complicated queries.

UNION [DISTINCT] and UNION ALL
INTERSECT [DISTINCT] and INTERSECT ALL
EXCEPT [DISTINCT] or MINUS [DISTINCT] and EXCEPT ALL, MINUS ALL 

The syntax for a set operation is:

<SELECT-statement>
{UNION | INTERSECT | EXCEPT | MINUS} [ALL | DISTINCT]
<SELECT-statement>
{UNION | INTERSECT | EXCEPT | MINUS} [ALL | DISTINCT]
<SELECT-statement>]*
[ORDER BY …]
[LIMIT …]

Example 1: sql server union example

Select Id, Name, Gender from TableA
UNION
Select Id, Name, Gender from TableB

sql server union all example

Select Id, Name, Gender from TableA
UNION ALL
Select Id, Name, Gender from TableB

sql server intersect example

Select Id, Name, Gender from TableA
INTERSECT
Select Id, Name, Gender from TableB

sql server Except example

Select Id, Name, Gender from TableA
EXCEPT
Select Id, Name, Gender from TableB

Example 2: Consider the tables Invoices to the right and Students to the left. The Bills table contains invoices for building work performed by specific individuals, with some names appearing many times. The Students table contains a list of student names. In both tables, certain names occur.

For instance, we would want to know the names of all non-students working on building projects. Between any two queries, the UNION, EXCEPT, and INTERSECT operators can generate Boolean combinations. The following examples demonstrate how the operators interact with two SELECT queries.

1. Union

< SELECT query > UNION < query > - Return a table of all records found either in the < SELECT query > results or in the < query > results.

SELECT Name FROM Invoices
UNION SELECT Name FROM Students;

The query above provides a table containing all names discovered in either the Invoices or Students tables, with duplicates removed. We utilise ALL to keep duplicates out of the results table:

SELECT Name FROM Invoices
UNION ALL SELECT Name FROM Students;

2. Intersect

< SELECT query> INTERSECT < query> - Return a table of all records found both in the < SELECT query> results and also in the < query> results.

SELECT Name FROM Invoices
INTERSECT SELECT Name FROM Students;

3. Except

< SELECT query> EXCEPT < query> - Return a table of all records in the < SELECT query> results but not in the < query> results.

SELECT Name FROM Invoices
EXCEPT SELECT Name FROM Students;

Tables and columns can be given a temporary name using SQL Aliases.

These aliases only exist for the duration of the query in which they are utilized. To make aliases, we utilise the "AS" operator.

Example 1: We'll use the following query to get all of the information on the employees and managers, and then categorize them according to their jobs.

SELECT 'Employee' AS Type, Name, Dept_ID, Salary
FROM Employee_dept
UNION
SELECT 'Manager', Name, Dept_ID, Salary
From Managaer;

We've established a temporary column called "Type" in the above query, which will be used to classify the data as employee or management data.

In the temporary "Type" column of the UNION result, the supervisors are designated as "Manager" and their subordinates as "Employee."

Example 2: let us write a query to list all the unique employees and projects.

SELECT 'Employee' AS Type, Name, City, Country
FROM Employees
UNION
SELECT 'Project', Name, City, Country
FROM Projects;

Example 3: wrap everything around a single SELECT and alias that column with what you want.

select a as [b] -- just alias it here with whatever you want
from (
    select 1 as a 
    union  
    select 2 as b
    ...) result_set

But as @Will said, it is what it is, can't change that.

OR:

Simply use the query with the "alias" you want at the beginning and UNION it with a single SELECT statement that contains all of your other searches and values.

select 2 as b

union

select a
from (
    select 1 as a
    union
    select 3 as c
    union
    ... ) result_set

Example 1: I'm receiving an issue when I use an IF EXISTS clause on a group of UNION.

I can run the queries separately without issues, but when I try to run them together, I get an error:

SELECT
    '1.5.2- Customers' as INPUT,
    count (distinct ID) as Z,
    PRO_DATE,
    Month(PRO_DATE) as P_MONTH,
    Year(PRO_DATE) as P_YEAR
FROM
    [BASE]
WHERE
    BASE.CAT_CUSTO = 'EMPLO' and
    BASE.ESTAT = 'EX'
group by
    PRO_DATE

UNION 

IF  EXISTS (
    SELECT
        '1.6- OTHER CUSTOMERS' as INPUT,
        count (distinct ID)  as Z,
        PRO_DATE,
        Month(PRO_DATE) as P_MONTH,
        Year(PRO_DATE) as P_YEAR
    FROM
        [BASE]
    WHERE
        BASE.CAT_CUSTO <> 'EMPLO'
    group by
        PRO_DATE
)  begin
SELECT
        '1.6- OTHER CUSTOMERS' as INPUT,
        count (distinct ID)  as Z,
        PRO_DATE,
        Month(PRO_DATE) as P_MONTH,
        Year(PRO_DATE) as P_YEAR
    FROM
        [BASE]
    WHERE
        BASE.CAT_CUSTO <> 'EMPLO'
    group by
        PRO_DATE

 end
ELSE
    SELECT TOP 1
        '1.6- OTHER CUSTOMERS' as INPUT,
        0 as Z,
        PRO_DATE,
        Month(PRO_DATE) as P_MONTH,
        Year(PRO_DATE) as P_YEAR
    FROM
        [BASE]

Example 2: Because the WHERE clause is unique to each each SELECT, you'll need to change yours to the first SELECT like this:

SELECT PubKey, Title FROM Publication
WHERE EXISTS (
    SELECT * 
      FROM UserPublication 
     WHERE UserPublication.PubKey = Publication.PubKey
)
UNION
SELECT NoteKey, Title FROM Note

Example 1: It's not that an alias is required, INSERT INTO tableA SelectQueryB; when SelectQuery is a UNION query, even though the SQL standard does not require them. I think that this works, too (and doesn't need an alias):

INSERT INTO AssetControl  
(SELECT * FROM CSVImport  
UNION  
SELECT * FROM AssetTemp);

Example 2:

INSERT   INTO PENALTIES
SELECT   PAYMENTNO + 1000, EmployeeNO, PAYMENT_DATE, AMOUNT
 FROM     PENALTIES
 WHERE    AMOUNT >
     (SELECT   AVG(AMOUNT)
      FROM     PENALTIES)
UNION
SELECT   PAYMENTNO + 2000, EmployeeNO, PAYMENT_DATE, AMOUNT
FROM     PENALTIES
WHERE    EmployeeNO = 27;

Example 3: There are three tables: T1, T2, T_target. T1 and T2 table have many different columns but I need only the ID column from both. The T_target table has an ID column of course and another: project_No.

There are some IDs which appears in T1 and T2 too, but I don't want to create duplicates between them, if an ID appears in both table it have to be inserted into the T_target only once but if it is already in the T_target it's allowed to act twice. The other of the criteria is every newly inserted ID must be value 21 in 'project_No' column:

T1:

T2:

Query:

INSERT INTO T_target
   (ID,
   project_No)
SELECT ID,
       21
FROM   T1
WHERE  ID IS NOT NULL
UNION
SELECT ID,
       21
FROM   T2
WHERE  ID IS NOT NULL 

Output:

T_target:

ID     project_No
2976   1
3331   7
4049   7
5426   8
5915   3
6253   10

When assessing duplicates, the SQL UNION operator considers all NULL values as a single NULL value. The ALL or DISTINCT operators are optional when combined with UNION. Duplicate rows can appear in the combined result set when using the ALL operator.

Syntax:

UNION ALL, unlike UNION in standard SQL, does not eliminate duplicate records. When using UNION, NULL IS NULL removes duplicate rows.

select null
union
select null

Example 1: You could supply a dummy column in lieu of the missing one that returns NULL as below.

SELECT ID,
       Name,
       Age
FROM   TABLE_A
UNION ALL
SELECT ID,
       Name,
       NULL
FROM   TABLE_B

Example 2: If a column in one of the SELECTs is NULL, the corresponding columns in the other SELECTs are not always returned appropriately when using a UNION :

SELECT Avg(Value), Min(Value), Max(Value)
FROM Data
WHERE GroupId IS NOT NULL
GROUP BY GroupId 
UNION
SELECT Value, NULL, NULL
FROM Data 
WHERE GroupId IS NULL

Example 3: The null values are automatically included in the result when we use Union :

SELECT * FROM DataFlair_emp1 ;

Let us again try to view all the mail id’s after entering null values in our database.

SELECT  email FROM dataflair_emp1 
UNION All 
SELECT email as name FROM dataflair_emp2 
order by email

To self-join or combine the table, you can always use the Union operator.

Step 1 : Create table

CREATE TABLE COMMITTEE_MEMBERS
    (EmployeeNO       INTEGER      NOT NULL,
    BEGIN_DATE     DATE         NOT NULL,
    END_DATE       DATE                 ,
    POSITION       CHAR(20)             ,
    PRIMARY KEY    (EmployeeNO, BEGIN_DATE));

Step 2: Insert record

INSERT INTO COMMITTEE_MEMBERS VALUES (  6, '1990-01-01', '1990-12-31', 'Secretary');

INSERT INTO COMMITTEE_MEMBERS VALUES (  6, '1991-01-01', '1992-12-31', 'Member');

INSERT INTO COMMITTEE_MEMBERS VALUES (  6, '1992-01-01', '1993-12-31', 'Treasurer');

INSERT INTO COMMITTEE_MEMBERS VALUES (  6, '1993-01-01',  NULL, 'Chairman');

INSERT INTO COMMITTEE_MEMBERS VALUES (  2, '1990-01-01', '1992-12-31', 'Chairman');

INSERT INTO COMMITTEE_MEMBERS VALUES (  2, '1994-01-01',  NULL, 'Member');

INSERT INTO COMMITTEE_MEMBERS VALUES (112, '1992-01-01', '1992-12-31', 'Member');

INSERT INTO COMMITTEE_MEMBERS VALUES (112, '1994-01-01',  NULL, 'Secretary');

INSERT INTO COMMITTEE_MEMBERS VALUES (  8, '1990-01-01', '1990-12-31', 'Treasurer');

INSERT INTO COMMITTEE_MEMBERS VALUES (  8, '1991-01-01', '1991-12-31', 'Secretary');

INSERT INTO COMMITTEE_MEMBERS VALUES (  8, '1993-01-01', '1993-12-31', 'Member');

INSERT INTO COMMITTEE_MEMBERS VALUES (  8, '1994-01-01',  NULL, 'Member');

INSERT INTO COMMITTEE_MEMBERS VALUES ( 57, '1992-01-01', '1992-12-31', 'Secretary');

INSERT INTO COMMITTEE_MEMBERS VALUES ( 27, '1990-01-01', '1990-12-31', 'Member');

INSERT INTO COMMITTEE_MEMBERS VALUES ( 27, '1991-01-01', '1991-12-31', 'Treasurer');

INSERT INTO COMMITTEE_MEMBERS VALUES ( 27, '1993-01-01', '1993-12-31', 'Treasurer');

INSERT INTO COMMITTEE_MEMBERS VALUES ( 95, '1994-01-01',  NULL, 'Treasurer');

Step 3: Display records

SELECT EmployeeNO, BEGIN_DATE
 FROM COMMITTEE_MEMBERS
 UNION
SELECT EmployeeNO, END_DATE
 FROM COMMITTEE_MEMBERS
 ORDER BY EmployeeNO;

Output:

+------------+------------+
| EmployeeNO | BEGIN_DATE |
+------------+------------+
|          2 | 1992-12-31 |
|          2 | 1990-01-01 |
|          2 | NULL       |
|          2 | 1994-01-01 |
|          6 | 1990-12-31 |
|          6 | 1992-12-31 |
|          6 | 1993-12-31 |
|          6 | NULL       |
|          6 | 1990-01-01 |
|          6 | 1991-01-01 |
|          6 | 1992-01-01 |
|          6 | 1993-01-01 |
|          8 | 1990-12-31 |
|          8 | 1991-12-31 |
|          8 | 1993-12-31 |
|          8 | 1990-01-01 |
|          8 | NULL       |
|          8 | 1991-01-01 |
|          8 | 1993-01-01 |
|          8 | 1994-01-01 |
|         27 | 1993-01-01 |
|         27 | 1990-12-31 |
|         27 | 1991-12-31 |
|         27 | 1990-01-01 |
|         27 | 1993-12-31 |
|         27 | 1991-01-01 |
|         57 | 1992-01-01 |
|         57 | 1992-12-31 |
|         95 | 1994-01-01 |
|         95 | NULL       |
|        112 | 1992-01-01 |
|        112 | 1994-01-01 |
|        112 | 1992-12-31 |
|        112 | NULL       |
+------------+------------+

Step 4: drop records

drop table committee_members;

Example 2: In the below query The first query returns all the employees whose income is greater than or equal to 80000:

The second query selects employees whose occupation is professional:

SELECT [EmpID]
      ,[FirstName] + ' ' +[LastName] AS Name
	  ,[Education], [Occupation],
	  [YearlyIncome], [Sales]
  FROM [Employees 2015]
  WHERE YearlyIncome >= 80000
UNION
SELECT [EmpID]
      ,[FirstName] + ' ' +[LastName] AS Name
	  ,[Education], [Occupation], 
	  [YearlyIncome], [Sales]
  FROM [Employees 2015]
  WHERE Occupation = 'Professional'

We combine data that meets a requirement using the where clause and the union clause. To acquire data that matches the conditional statement, conditions are passed in the where clause.

To filter out the entries being merged in the UNION, use the WHERE clause in one or both of the SELECT queries.

Syntax:

SELECT * FROM tableName1 Where condition
UNION
SELECT * FROM tableName2 Where condition ;

Example 1: We'll enter the following into the "Employee dept" and "Manager" tables to get employee and manager names and salaries that are greater than 60,000:

SELECT Name, Salary
From Employee_dept
WHERE Salary>60000
UNION
SELECT NAME, Salary
FROM Manager
WHERE Salary>60000;

Example 2: UNION ALL with WHERE clause

Write a query that pulls Indian cities and postal codes from the Employees and Projects tables, with duplicate entries allowed:

SELECT City, PostalCode, Country FROM Employees
WHERE Country='India'
UNION ALL
SELECT City, PostalCode, Country FROM Projects
WHERE Country='India'
ORDER BY City;

Output:

City	PostalCode	Country
Delhi	110006		India
Mumbai	400015		India
Mumbai	400015		India
Mumbai	400015		India 

Example 3: Let us find employee emails with age more than 27 and a salary less than 30000.

SELECT  salary,email FROM dataflair_emp1 Where salary < 30000
UNION All 
SELECT age,email as name FROM dataflair_emp2 where age > 27
order by email