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.


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

Search Keys

  • sql union where
  • sql union statement
  • sql server union all
  • sql union example
  • sql multiple union
  • union of two queries
  • select from union query
  • union query example
  • union all syntax
  • intersection table
  • select intersect
  • sql all operator
  • sql join and union