Using EXCEPT and UNION combination to select uncommon values from two tables on SQL server

I came across a problem from my client where he wanted me to prepare the list of all the uncommon tables in two user databases. In other words – I was required to show only the user table which is present in one of the databases and exclude the user table which is present in both of them.

This was easily done by me, with the help of EXCEPT and UNION commands in SQL server.

I have created the below sample example which I successfully ran on my local SQL Server. Steps are:

1.       Databases test1 and test2 are created.
2.       Tables tab1, tab2 are created in database test1 and tables tab2, tab3 are created in database test2.
3.       Uncommon tables are extracted with the help of EXCEPT and UNION command.

1. CREATION OF DATABASES test1 AND test2: –

–Create database test1
Create database test1
use test1
–Create database test2
Create database test2
use test2

2. CREATE TABLES in DATABASES test1 AND test2:-

–Create table tab1 and tab2 in database test1
use test1
Create table tab1
(one varchar (1),
two varchar (1))

Create table tab2
(one varchar (1),
two varchar (1))

–Create table tab2 and tab3 in database test2
use test2
Create table tab2
(one varchar (1),
two varchar (1))

Create table tab3
(one varchar (1),
two varchar (1))

3. USE OF EXCEPT AND UNION TO PUBLISH ONLY UNCOMMON TABLES: –

–Find Uncommon table from test1 by comparing test1 and test2
Select A.name as TableName, ‘Test1’ as [Database] from
(
Select name from test1.sys.tables
Except
Select name from test2.sys.tables
) A
union — combines the results of both the except statements

–Find Uncommon table from test2 by comparing test2 and test1
Select B.name as TableName, ‘Test2’ as [Database] from 
(
Select name from test2.sys.tables
Except
Select name from test1.sys.tables
) B

OUTPUT OF ABOVE EXCEPT AND UNION QUERY: –

You can Notice in the above example – the query has provided only the list of uncommon tables tab1 and tab3 and excluded the common table tab2.

By |2019-01-31T13:37:36+00:00January 31st, 2019|Microsoft SQL|

About the Author:

SCALABILITY ENGINEERS PVT. LTD.

WE ARE ONE-STOP SOLUTION FOR YOUR MICROSOFT SQL SERVER MANAGEMENT.

LET’S TALK

CONTACT US

 Adrian Clayborn Authentic Jersey