How to use union in sql server

UNION clause in SQL server.

UNION clause: It use used to combine the tables and display the single result. Sometimes user want to display single result from multiple tables. As this situation the user can combine tow or more tables and display result as a requirement. UNION clause is very useful while combining two or more tables.

While using UNION clause the number of columns are same.

UNION Clause Syntax:

SELECT column_name(s) FROM table1

UNION

SELECT column_name(s) FROM table 2

UNION Clause Examples:

Here are first table "Supplier" And other is "Customer"

Create Table Supplier
(
Code varchar(10)NOT NULL,
Name Varchar(20)NOT NULL,
ContactPerson Varchar(20)NOT NULL,
City Varchar(15)NOT NULL,
Country Varchar(20)NOT NULL
)

Insert into Supplier Values('001','Mirror pvt.ltd.','Ammy','NY','USA')
Insert into Supplier Values('002','PNA pvt. ltd.','Pina','CA','USA')
Insert into Supplier Values('003','Single pvt. ltd.','Sumi','Alabama','USA')
Insert into Supplier Values('004','Yours pvt. ltd.','Don','NW','USA')
Insert into Supplier Values('005','Strong pvt. ltd.','Mik','NBA','USA')
Insert into Supplier Values('006','My center pvt. ltd.','Sweet','DK','USA')





Create Table Customer
(
CID Varchar(10)NOT NULL,
Name Varchar(30)NOT NULL,
City Varchar(20)NOT NULL,
Phone Varchar(30)NOT NULL,
Country Varchar(30)NOT NULL
)

Insert into Customer Values('001','Lili','Alabama','(2010) 205-764','USA')
Insert into Customer Values('002','Rock','Colorado','(2010) 285-068','USA')
Insert into Customer Values('003','Lock','Illinois','(2010) 173-514','USA')
Insert into Customer Values('004','Hono','Michigan','(2010) 84-913','USA')
Insert into Customer Values('005','Spring','Minnesota','(2010) 154-637','USA')

1. Display city name from "Supplier" and "Customer" table.

Select City from Supplier
Union
Select City from Customer

Result

City

Alabama

CA

Colorado

DK

Illinois

Michigan

Minnesota

NBA

NW

NY

2. Display city and country name from "Supplier" and "Customer" tables.

Select City,Country from Supplier
Union
Select City,Country from Customer

Result

City	Country
Alabama	USA
CA	USA
Colorado	USA
DK	USA
Illinois	USA
Michigan	USA
Minnesota	USA
NBA	USA
NW	USA
NY	USA

3. Display city and country name from "Supplier" and "Customer" tables with ORDER BY clause.

Select City,Country from Supplier
Union
Select City,Country from Customer order by City asc

Result

City	Country
Alabama	USA
CA	USA
Colorado	USA
DK	USA
Illinois	USA
Michigan	USA
Minnesota	USA
NBA	USA
NW	USA
NY	USA

4. Display city and country name from "Supplier" and "Customer" tables with WHERE clause.

Select City,Country from Supplier Where City='NY'
Union
Select City,Country from Customer Where City='Alabama'

Result

City	Country
Alabama	USA
NY	USA

UNION clause in video

UNION clause in Image_1

UNION clause in Image_2