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.

You can also search these topics using difference between union and union all, sql union vs union all, union all order by, select union order by, oracle select union, union sql example, sql union all example.

SQL UNION Syntax


SELECT column_name1, column_name2...column_nameN FROM table_name1
UNION
SELECT column_name1, column_name2...column_nameN FROM table_name2;

Note: The SQL UNION operator selects only unique values by default. To allow duplicate values, use the ALL keyword with UNION opertaor.

SQL UNION ALL Syntax


SELECT column_name1, column_name2...column_nameN FROM table_name1
UNION ALL
SELECT column_name1, column_name2...column_nameN FROM table_name2;

Note: The field names in the results of a UNION are usually equal to the column names in the first SQL SELECT query in the UNION.

Sample Database Table - Books

BookId	BookName	BookPrice	DomainName
101	Sql Complete Reference	250.5	Database
102	Sql Commands	120	Database
103	Pl Sql Quick Programming	150	Programming
104	Sql Query Injection	199.99	Security
105	The Power Of Pl Sql	220	Programming

Sample Database Table - Author

AuthorId	AuthorName	Gender	DomainName
111	Suresh Babu	Male	Hacking
222	Siva Kumar	Male	Database
333	Azagu Bala Haris	Male	Programming
444	Varshini Kutty	Female	Database

Note:- There must be a common columns while selecting columns from multiple tables. In the "Book1" and "Book2" both tables contains a common column called "BookID".

SQL UNION - Example

The following SQL statement selects all the unique domain names (only distinct values) from the "Books" and the "Author" tables:


SELECT DomainName FROM Books
UNION
SELECT DomainName FROM Author
ORDER BY DomainName;

The result of above query is:

DomainName
Database
Hacking
Programming
Security

Note: UNION operator cannot be used to list ALL domain names from the two tables. If several books and author share the same domain name, each domain name will only be listed once. Use UNION ALL operator to also select duplicate values!

SQL UNION ALL - Example

The following SQL statement uses UNION ALL to select all (duplicate values also) from the "Books" and the "Author" tables:


SELECT DomainName FROM Books
UNION ALL
SELECT DomainName FROM Author
ORDER BY DomainName;

The result of above query is:

DomainName
Database
Database
Database
Database
Hacking
Programming
Programming
Programming
Security

You can also search these topics using sql union statement, sql server union all, sql union example, sql multiple union, union of two queries, select from union query, union query example, sql join and union.