{"id":4312,"date":"2017-11-10T08:41:53","date_gmt":"2017-11-10T08:41:53","guid":{"rendered":"http:\/\/vivegroups.com\/?p=4312"},"modified":"2022-08-28T15:33:13","modified_gmt":"2022-08-28T15:33:13","slug":"sql-union-operator","status":"publish","type":"post","link":"https:\/\/iteducation.vivegroups.com\/sql-union-operator\/","title":{"rendered":"SQL UNION Operator"},"content":{"rendered":"

SQL UNION Operator :\u00a0SQL UNION Operator is used to combine the result-set of two or more SELECT statements.<\/p>\n

Each SELECT statement within UNION must have the same number of columns<\/li>\n
The columns must also have similar data types<\/li>\n

The columns in each SELECT statement must also be in the same order<\/li>\n<\/ul>\n

UNION Syntax<\/h3>\n
SELECT\u00a0column_name(s)<\/em>\u00a0FROM\u00a0table1<\/em><\/strong>
\n UNION<\/strong>
\n SELECT\u00a0column_name(s)<\/em>\u00a0FROM\u00a0table2<\/em>;<\/strong><\/p>\n

UNION ALL Syntax<\/h3>\n
The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL:<\/p>\n
SELECT\u00a0column_name(s)<\/em>\u00a0FROM\u00a0table1<\/em><\/strong>
\n UNION\u00a0ALL<\/strong>
\n SELECT\u00a0column_name(s)<\/em>\u00a0FROM\u00a0table2<\/em>;<\/strong><\/p>\n
Note:<\/strong><\/p>\n
The column names in the result-set are usually equal to the column names in the first SELECT statement in the UNION.<\/p>\n
Demo Database<\/h2>\n
In this tutorial we will use the well-known Northwind sample database.<\/p>\n
Below is a selection from the “Customers” table:<\/p>\n\n\n\n\n\n\n
CustomerID<\/strong><\/td>\n CustomerName<\/strong><\/td>\n ContactName<\/strong><\/td>\n Address<\/strong><\/td>\n City<\/strong><\/td>\n PostalCode<\/strong><\/td>\n Country<\/strong><\/td>\n<\/tr>\n
1<\/td>\n Alfreds Futterkiste<\/td>\n Maria Anders<\/td>\n Obere Str. 57<\/td>\n Berlin<\/td>\n 12209<\/td>\n Germany<\/td>\n<\/tr>\n
2<\/td>\n Ana Trujillo Emparedados y helados<\/td>\n Ana Trujillo<\/td>\n Avda. de la Constituci\u00f3n 2222<\/td>\n M\u00e9xico D.F.<\/td>\n 05021<\/td>\n Mexico<\/td>\n<\/tr>\n
3<\/td>\n Antonio Moreno Taquer\u00eda<\/td>\n Antonio Moreno<\/td>\n Mataderos 2312<\/td>\n M\u00e9xico D.F.<\/td>\n 05023<\/td>\n Mexico<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
And a selection from the “Suppliers” table:<\/p>\n\n\n\n\n\n\n
SupplierID<\/strong><\/td>\n SupplierName<\/strong><\/td>\n ContactName<\/strong><\/td>\n Address<\/strong><\/td>\n City<\/strong><\/td>\n PostalCode<\/strong><\/td>\n Country<\/strong><\/td>\n<\/tr>\n
1<\/td>\n Exotic Liquid<\/td>\n Charlotte Cooper<\/td>\n 49 Gilbert St.<\/td>\n London<\/td>\n EC1 4SD<\/td>\n UK<\/td>\n<\/tr>\n
2<\/td>\n New Orleans Cajun Delights<\/td>\n Shelley Burke<\/td>\n P.O. Box 78934<\/td>\n New Orleans<\/td>\n 70117<\/td>\n USA<\/td>\n<\/tr>\n
3<\/td>\n Grandma Kelly’s Homestead<\/td>\n Regina Murphy<\/td>\n 707 Oxford Rd.<\/td>\n Ann Arbor<\/td>\n 48104<\/td>\n USA<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
SQL UNION Example<\/h2>\n
The following SQL statement selects all the different cities (only distinct values) from “Customers” and “Suppliers”:<\/p>\n
Example<\/h3>\n
SELECT\u00a0City\u00a0FROM\u00a0Customers
\nUNION
\nSELECT\u00a0City\u00a0FROM\u00a0Suppliers
\nORDER\u00a0BY\u00a0City;<\/p>\n
<\/p>\n
Output<\/strong><\/h4>\n
Number of Records: 7<\/p>\n\n\n\n\n\n\n\n\n\n\n\n
City<\/strong><\/td>\n<\/tr>\n
Aachen<\/td>\n<\/tr>\n
Albuquerque<\/td>\n<\/tr>\n
Anchorage<\/td>\n<\/tr>\n
Ann Arbor<\/td>\n<\/tr>\n
Annecy<\/td>\n<\/tr>\n
Barcelona<\/td>\n<\/tr>\n
Barquisimeto<\/td>\n<\/tr>\n
Bend<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
<\/p>\n
Note:<\/strong><\/p>\n
If some customers or suppliers have the same city, each city will only be listed once, because UNION selects only distinct values. Use UNION ALL to also select duplicate values!<\/p>\n
SQL UNION ALL Example<\/h2>\n
The following SQL statement selects all cities (duplicate values also) from “Customers” and “Suppliers”:<\/p>\n
Example<\/h3>\n
SELECT\u00a0City\u00a0FROM\u00a0Customers
\nUNION\u00a0ALL
\nSELECT\u00a0City\u00a0FROM\u00a0Suppliers
\nORDER\u00a0BY\u00a0City;<\/p>\n
<\/p>\n
Output<\/strong><\/h4>\n
Number of Records: 7<\/p>\n\n\n\n\n\n\n\n\n\n\n
City<\/strong><\/td>\n<\/tr>\n
Aachen<\/td>\n<\/tr>\n
Albuquerque<\/td>\n<\/tr>\n
Anchorage<\/td>\n<\/tr>\n
Ann Arbor<\/td>\n<\/tr>\n
Annecy<\/td>\n<\/tr>\n
Barcelona<\/td>\n<\/tr>\n
Barquisimeto<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
<\/p>\n
SQL UNION With WHERE<\/h2>\n
The following SQL statement selects all the different German cities (only distinct values) from “Customers” and “Suppliers”:<\/p>\n
Example<\/h3>\n
SELECT\u00a0City, Country\u00a0FROM\u00a0Customers
\nWHERE\u00a0Country=’Germany’
\nUNION
\nSELECT\u00a0City, Country\u00a0FROM\u00a0Suppliers
\nWHERE\u00a0Country=’Germany’
\nORDER\u00a0BY\u00a0City;<\/p>\n
<\/p>\n
Output<\/strong><\/h4>\n
Number of Records: 13<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
City<\/strong><\/td>\n Country<\/strong><\/td>\n<\/tr>\n
Aachen<\/td>\n Germany<\/td>\n<\/tr>\n
Berlin<\/td>\n Germany<\/td>\n<\/tr>\n
Brandenburg<\/td>\n Germany<\/td>\n<\/tr>\n
Cunewalde<\/td>\n Germany<\/td>\n<\/tr>\n
Cuxhaven<\/td>\n Germany<\/td>\n<\/tr>\n
Frankfurt<\/td>\n Germany<\/td>\n<\/tr>\n
Frankfurt a.M.<\/td>\n Germany<\/td>\n<\/tr>\n
K\u00f6ln<\/td>\n Germany<\/td>\n<\/tr>\n
Leipzig<\/td>\n Germany<\/td>\n<\/tr>\n
Mannheim<\/td>\n Germany<\/td>\n<\/tr>\n
M\u00fcnchen<\/td>\n Germany<\/td>\n<\/tr>\n
M\u00fcnster<\/td>\n Germany<\/td>\n<\/tr>\n
Stuttgart<\/td>\n Germany<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
<\/p>\n
SQL UNION ALL With WHERE<\/h2>\n
The following SQL statement selects all German cities (duplicate values also) from “Customers” and “Suppliers”:<\/p>\n
Example<\/h3>\n
SELECT\u00a0City, Country\u00a0FROM\u00a0Customers
\nWHERE\u00a0Country=’Germany’
\nUNION\u00a0ALL
\nSELECT\u00a0City, Country\u00a0FROM\u00a0Suppliers
\nWHERE\u00a0Country=’Germany’
\nORDER\u00a0BY\u00a0City;<\/p>\n
<\/p>\n
Output<\/strong><\/h4>\n
Number of Records: 14<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
City<\/strong><\/td>\n Country<\/strong><\/td>\n<\/tr>\n
Aachen<\/td>\n Germany<\/td>\n<\/tr>\n
Berlin<\/td>\n Germany<\/td>\n<\/tr>\n
Berlin<\/td>\n Germany<\/td>\n<\/tr>\n
Brandenburg<\/td>\n Germany<\/td>\n<\/tr>\n
Cunewalde<\/td>\n Germany<\/td>\n<\/tr>\n
Cuxhaven<\/td>\n Germany<\/td>\n<\/tr>\n
Frankfurt<\/td>\n Germany<\/td>\n<\/tr>\n
Frankfurt a.M.<\/td>\n Germany<\/td>\n<\/tr>\n
K\u00f6ln<\/td>\n Germany<\/td>\n<\/tr>\n
Leipzig<\/td>\n Germany<\/td>\n<\/tr>\n
Mannheim<\/td>\n Germany<\/td>\n<\/tr>\n
M\u00fcnchen<\/td>\n Germany<\/td>\n<\/tr>\n
M\u00fcnster<\/td>\n Germany<\/td>\n<\/tr>\n
Stuttgart<\/td>\n Germany<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Another UNION Example<\/h2>\n
The following SQL statement lists all customers and suppliers:<\/p>\n
Example<\/h3>\n
SELECT\u00a0‘Customer’\u00a0As\u00a0Type, ContactName, City, Country
\nFROM\u00a0Customers
\nUNION
\nSELECT\u00a0‘Supplier’, ContactName, City, Country
\nFROM\u00a0Suppliers;<\/p>\n
<\/p>\n
Output<\/h4>\n
Number of Records: 6<\/p>\n\n\n\n\n\n\n\n\n\n
Type<\/strong><\/td>\n ContactName<\/strong><\/td>\n City<\/strong><\/td>\n Country<\/strong><\/td>\n<\/tr>\n
Customer<\/td>\n Alejandra Camino<\/td>\n Madrid<\/td>\n Spain<\/td>\n<\/tr>\n
Customer<\/td>\n Alexander Feuer<\/td>\n Leipzig<\/td>\n Germany<\/td>\n<\/tr>\n
Customer<\/td>\n Ana Trujillo<\/td>\n M\u00e9xico D.F.<\/td>\n Mexico<\/td>\n<\/tr>\n
Customer<\/td>\n Anabela Domingues<\/td>\n S\u00e3o Paulo<\/td>\n Brazil<\/td>\n<\/tr>\n
Customer<\/td>\n Andr\u00e9 Fonseca<\/td>\n Campinas<\/td>\n Brazil<\/td>\n<\/tr>\n
Customer<\/td>\n Ann Devon<\/td>\n London<\/td>\n UK<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
SQL UNION Operator :\u00a0SQL UNION Operator is used to combine the result-set of two or more SELECT statements. Each SELECT statement within UNION must have<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[386],"tags":[450,451],"class_list":["post-4312","post","type-post","status-publish","format-standard","hentry","category-sql","tag-sql-union","tag-sql-union-operator"],"_links":{"self":[{"href":"https:\/\/iteducation.vivegroups.com\/wp-json\/wp\/v2\/posts\/4312","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/iteducation.vivegroups.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/iteducation.vivegroups.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/iteducation.vivegroups.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/iteducation.vivegroups.com\/wp-json\/wp\/v2\/comments?post=4312"}],"version-history":[{"count":1,"href":"https:\/\/iteducation.vivegroups.com\/wp-json\/wp\/v2\/posts\/4312\/revisions"}],"predecessor-version":[{"id":13188,"href":"https:\/\/iteducation.vivegroups.com\/wp-json\/wp\/v2\/posts\/4312\/revisions\/13188"}],"wp:attachment":[{"href":"https:\/\/iteducation.vivegroups.com\/wp-json\/wp\/v2\/media?parent=4312"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/iteducation.vivegroups.com\/wp-json\/wp\/v2\/categories?post=4312"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/iteducation.vivegroups.com\/wp-json\/wp\/v2\/tags?post=4312"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}

UNION Syntax<\/h3>\nSELECT\u00a0column_name(s)<\/em>\u00a0FROM\u00a0table1<\/em><\/strong>\n UNION<\/strong>\n SELECT\u00a0column_name(s)<\/em>\u00a0FROM\u00a0table2<\/em>;<\/strong><\/p>\n

SQL UNION Example<\/h2>\nThe following SQL statement selects all the different cities (only distinct values) from “Customers” and “Suppliers”:<\/p>\n

Example<\/h3>\nSELECT\u00a0City\u00a0FROM\u00a0Customers\nUNION\nSELECT\u00a0City\u00a0FROM\u00a0Suppliers\nORDER\u00a0BY\u00a0City;<\/p>\n <\/p>\n

Example<\/h3>\nSELECT\u00a0City\u00a0FROM\u00a0Customers\nUNION\u00a0ALL\nSELECT\u00a0City\u00a0FROM\u00a0Suppliers\nORDER\u00a0BY\u00a0City;<\/p>\n <\/p>\n

SQL UNION With WHERE<\/h2>\nThe following SQL statement selects all the different German cities (only distinct values) from “Customers” and “Suppliers”:<\/p>\n

Example<\/h3>\nSELECT\u00a0City, Country\u00a0FROM\u00a0Customers\nWHERE\u00a0Country=’Germany’\nUNION\nSELECT\u00a0City, Country\u00a0FROM\u00a0Suppliers\nWHERE\u00a0Country=’Germany’\nORDER\u00a0BY\u00a0City;<\/p>\n <\/p>\n

SQL UNION ALL With WHERE<\/h2>\nThe following SQL statement selects all German cities (duplicate values also) from “Customers” and “Suppliers”:<\/p>\n

Example<\/h3>\nSELECT\u00a0City, Country\u00a0FROM\u00a0Customers\nWHERE\u00a0Country=’Germany’\nUNION\u00a0ALL\nSELECT\u00a0City, Country\u00a0FROM\u00a0Suppliers\nWHERE\u00a0Country=’Germany’\nORDER\u00a0BY\u00a0City;<\/p>\n <\/p>\n

Another UNION Example<\/h2>\nThe following SQL statement lists all customers and suppliers:<\/p>\n

Example<\/h3>\nSELECT\u00a0‘Customer’\u00a0As\u00a0Type, ContactName, City, Country\nFROM\u00a0Customers\nUNION\nSELECT\u00a0‘Supplier’, ContactName, City, Country\nFROM\u00a0Suppliers;<\/p>\n <\/p>\n

UNION Syntax<\/h3>\n
SELECT\u00a0column_name(s)<\/em>\u00a0FROM\u00a0table1<\/em><\/strong>
\n UNION<\/strong>
\n SELECT\u00a0column_name(s)<\/em>\u00a0FROM\u00a0table2<\/em>;<\/strong><\/p>\n

SQL UNION Example<\/h2>\n
The following SQL statement selects all the different cities (only distinct values) from “Customers” and “Suppliers”:<\/p>\n

Example<\/h3>\n
SELECT\u00a0City\u00a0FROM\u00a0Customers
\nUNION
\nSELECT\u00a0City\u00a0FROM\u00a0Suppliers
\nORDER\u00a0BY\u00a0City;<\/p>\n
<\/p>\n

Example<\/h3>\n
SELECT\u00a0City\u00a0FROM\u00a0Customers
\nUNION\u00a0ALL
\nSELECT\u00a0City\u00a0FROM\u00a0Suppliers
\nORDER\u00a0BY\u00a0City;<\/p>\n
<\/p>\n

SQL UNION With WHERE<\/h2>\n
The following SQL statement selects all the different German cities (only distinct values) from “Customers” and “Suppliers”:<\/p>\n

Example<\/h3>\n
SELECT\u00a0City, Country\u00a0FROM\u00a0Customers
\nWHERE\u00a0Country=’Germany’
\nUNION
\nSELECT\u00a0City, Country\u00a0FROM\u00a0Suppliers
\nWHERE\u00a0Country=’Germany’
\nORDER\u00a0BY\u00a0City;<\/p>\n
<\/p>\n

SQL UNION ALL With WHERE<\/h2>\n
The following SQL statement selects all German cities (duplicate values also) from “Customers” and “Suppliers”:<\/p>\n

Example<\/h3>\n
SELECT\u00a0City, Country\u00a0FROM\u00a0Customers
\nWHERE\u00a0Country=’Germany’
\nUNION\u00a0ALL
\nSELECT\u00a0City, Country\u00a0FROM\u00a0Suppliers
\nWHERE\u00a0Country=’Germany’
\nORDER\u00a0BY\u00a0City;<\/p>\n
<\/p>\n

Another UNION Example<\/h2>\n
The following SQL statement lists all customers and suppliers:<\/p>\n

Example<\/h3>\n
SELECT\u00a0‘Customer’\u00a0As\u00a0Type, ContactName, City, Country
\nFROM\u00a0Customers
\nUNION
\nSELECT\u00a0‘Supplier’, ContactName, City, Country
\nFROM\u00a0Suppliers;<\/p>\n
<\/p>\n