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.
Related Links
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 |
Related Links