SQL - UNION Operator

---

---

The SQL UNION Operator

The SQL UNION operator is used to combine the result sets of two or more SELECT queries into a single result set. It removes duplicate rows by default, returning only distinct records.

• Each SELECT query must have the same number of columns.
• Corresponding columns must have compatible data types.
• The column names in the result set are taken from the first SELECT statement.
• Use UNION ALL if you want to include duplicate rows.

Syntax

The basic syntax of SQL UNION operator is as follows:

SELECT column1, column2, ...
FROM table1
WHERE condition

UNION  

SELECT column1, column2, ...
FROM table2
WHERE condition;

Here, the given condition could be any given expression based on your requirement.

SQL UNION on a Single Field

The SQL UNION operator can be applied to combine results of queries that return only a single column (single field) from one or more tables. This is useful when you want to create a combined list of unique values from different tables or queries.

Example

Assume we have created a table with name CUSTOMERS in MySQL database using CREATE TABLE statement as shown below:

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),       
   PRIMARY KEY (ID)
);

Following query inserts values into this table using the INSERT statement:

INSERT INTO CUSTOMERS VALUES
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00),
(2, 'Khilan', 25, 'Delhi', 1500.00),
(3, 'Kaushik', 23, 'Kota', 2000.00),
(4, 'Chaitali', 25, 'Mumbai', 6500.00),
(5, 'Hardik', 27, 'Bhopal', 8500.00),
(6, 'Komal', 22, 'Hyderabad', 4500.00),
(7, 'Muffy', 24, 'Indore', 10000.00);

The CUSTOMERS table is as follows:

Now, creating the second table ORDERS using CREATE TABLE statement as shown below:

CREATE TABLE ORDERS (
   OID INT NOT NULL,
   DATE DATETIME NOT NULL,
   CUSTOMER_ID INT NOT NULL,
   AMOUNT INT NOT NULL,      
   PRIMARY KEY (OID)
);

Following query inserts values into this table using the INSERT statement:

INSERT INTO ORDERS VALUES
(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);

The ORDERS table is as follows:

Using the following query, let us combine the SALARY and AMOUNT columns from CUSTOMERS and ORDERS table (since these columns have similar datatypes):

SELECT SALARY FROM CUSTOMERS UNION SELECT AMOUNT FROM ORDERS;

Output of the above query is as follows:

SQL UNION on Multiple Fields

The SQL UNION operator can also be used to combine results from multiple columns (multiple fields) across different tables or queries. This is useful when you want to create a consolidated dataset containing several columns from multiple sources.

When using UNION on multiple fields, the number and order of the columns in each SELECT statement must match. Additionally, the data types of the corresponding columns must be compatible for the UNION to work correctly. If the data types are not compatible, you can use conversion functions such as CAST or CONVERT to align them.

Example

As the CUSTOMERS and ORDERS tables are not union-compatible individually, let us first join these two tables into a bigger table using Left Join and Right Join. The joined tables retrieved will have same number of columns with same datatypes, becoming union compatible. Now, these tables are combined using UNION query shown below:

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:

SQL UNION with WHERE Clause

The SQL UNION operator can be used with WHERE clauses in the individual SELECT statements to filter rows before combining the results. Each query can have its own WHERE condition, allowing you to select specific subsets of data from multiple tables.

Syntax

Following is the syntax for using the SQL WHERE clause with UNION operator:

SELECT column1, column2, ...
FROM table1
WHERE condition1
UNION
SELECT column1, column2, ...
FROM table2
WHERE condition2;

Example

In the following query, we are retrieving the id's of the customers where id is greater than 5 and 2 from the 'CUSTOMERS' and 'ORDERS' tables respectively:

SELECT ID, SALARY FROM CUSTOMERS WHERE ID > 5
UNION
SELECT CUSTOMER_ID, AMOUNT FROM ORDERS WHERE CUSTOMER_ID > 2;

Following is the result produced:

SQL UNION with ORDER BY Clause

When we use UNION with ORDER BY clause, it combines the sorted result sets of all SELECT statements and produces a single sorted result set.

The SQL UNION operator can be combined with an ORDER BY clause to sort the final result set after merging multiple SELECT queries. The ORDER BY applies to the combined results, not to individual queries.

Syntax

Following is the syntax for using the SQL ORDER BY clause with UNION operator:

SELECT column1, column2, ...
FROM table1
WHERE condition1
UNION
SELECT column1, column2, ...
FROM table2
WHERE condition2
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC];

Example

In here, we are retrieving the id's of the customers where id is greater than 5 and 2 from the 'CUSTOMERS' and 'ORDERS' tables respectively, sorted low to high from their salary:

SELECT ID, SALARY FROM CUSTOMERS WHERE ID > 5
UNION
SELECT CUSTOMER_ID, AMOUNT FROM ORDERS WHERE CUSTOMER_ID > 2
ORDER BY SALARY;

Following is the output of the above query:

The ORDER BY clause in a UNION statement applies to the entire result set, not just the last SELECT statement.

UNION with Aliases

When using the SQL UNION operator, you can assign column aliases in the first SELECT statement to give names to the columns in the combined result set. These aliases apply to the entire union, while any aliases in the subsequent SELECT statements are ignored.

Aliases provide temporary names for columns or tables, which is helpful when working with multiple tables or columns that have similar names.

It is important to remember that the final column names in the union are determined by the first SELECT statement. To ensure consistent naming across all queries, use column aliases in the first SELECT when combining results with UNION.

Syntax

Following is the syntax for using Union with Aliases:

SELECT column1 AS alias1, column2 AS alias2, ...
FROM table1
WHERE condition1
UNION
SELECT column1 AS alias3, column2, ...
FROM table2
WHERE condition2;

Example

The following query retrieves all the id's from both tables, along with an indication of whether each id is of the customer or the order made by them:

SELECT ID, 'customer' AS type FROM CUSTOMERS
UNION
SELECT OID, 'order' AS type FROM ORDERS;

Following is the output produced:

SQL UNION ALL Operator

The SQL UNION ALL operator is similar to the UNION operator, but unlike UNION, it does not remove duplicate rows. It combines the results of two or more SELECT statements and returns all rows, including duplicates.

This makes UNION ALL faster than UNION because it does not need to check for or remove duplicate rows.

• Each SELECT must have the same number of columns.
• Data types of the columns must be compatible.
• Use UNION ALL when you want to include all occurrences of rows, including duplicates.

Syntax

Following is the syntax of SQL UNION ALL operator:

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

Example

In the following example, we retrieve the SALARY values from the CUSTOMERS table and the AMOUNT values from the ORDERS table, including duplicate values:

SELECT SALARY FROM CUSTOMERS
UNION ALL
SELECT AMOUNT FROM ORDERS;

This query returns a combined list of salaries and order amounts, including repeated values:

SQL UNION ALL with WHERE Clause

You can use the UNION ALL operator in combination with individual WHERE clauses for each SELECT statement to retrieve and merge specific subsets of data without removing duplicates.

Syntax

Following is the syntax of SQL UNION ALL operator with the WHERE clause:

SELECT column1, column2, ...
FROM table1
WHERE condition1
UNION ALL
SELECT column1, column2, ...
FROM table2
WHERE condition2;

Example

The following query retrieves all entries with ID greater than 5 from the CUSTOMERS table and all entries with CUSTOMER_ID greater than 2 from the ORDERS table, preserving duplicates:

SELECT ID, SALARY FROM CUSTOMERS WHERE ID > 5
UNION ALL
SELECT CUSTOMER_ID, AMOUNT FROM ORDERS WHERE CUSTOMER_ID > 2;

This would produce the following result, showing all matching rows including duplicates:

Important Points About SQL UNION Operator

While using the UNION or UNION ALL operators, it is important to keep a few points in mind to avoid errors:

• Column Count Must Match: Each SELECT in a UNION must return the same number of columns.
• Data Types Should Be Compatible: The columns in each SELECT should have similar or compatible data types to avoid casting or errors.
• ORDER BY Goes at the End: If used, the ORDER BY clause should be placed at the end of the final SELECT statement, as it applies to the full result set.
• Aliases Matter: Column aliases defined in the first SELECT statement are used as the column names in the final result set. Later SELECT aliases are ignored.
• Use UNION ALL for Performance: If you don't need to remove duplicates, prefer UNION ALL for better query performance, especially with large datasets.
• Nulls Are Considered Duplicates: When using UNION, rows containing NULLs are also considered for duplicate elimination.
• Functions and Expressions: You can use functions or expressions in SELECT queries within UNION, but be mindful of how they affect data types and column compatibility.

There are two other operators which are like the UNION operator.

• SQL INTERSECT Operator: This is used to combine two SELECT statements, but returns rows only from the first SELECT statement that are identical to a row in the second SELECT statement.

• SQL EXCEPT Operator: This combines two SELECT statements and returns rows from the first SELECT statement that are not returned by the second SELECT statement.