Link to home
Start Free TrialLog in
Avatar of tnagasatish
tnagasatish

asked on

Intersection and minus in MSSQL

I have nearly 50 pairs of tables. Each table has nearly 20 columns. I would like to find out the number of rows that are common in each pair of tables. I also would like to findout the count of minus operation. I cannot use EXISTS and NOT EXISTS, because there are 20 columns, and 50 tables. I know that there is no direct operator for intersection, and minus in MSSQL. Is there any hacky way to findout this without writing complex program.
Avatar of Hilaire
Hilaire
Flag of France image

>>I cannot use EXISTS and NOT EXISTS, because there are 20 columns, and 50 tables.<<
Are you telling us that there's no primary key / unique indexes defined on your tables ?

To perform INTERSECT :
make 2 sub queries and make an inner join between the two

To perform Minus
make 2 sub queries and make a full outer join between the two, filtering to keep only
rows from a where b.column is null
and
rows from b where a.column is null

Don't think of anything else for the moment ...

Hilaire
Avatar of Lowfatspread
i agree with hilaire...

you could always generate the sql for the tables by reference to information schema columns/tables

have you considered

Select x.*
From (
select 'tab1' as tabnam,a.* from table1 as  A
union
select 'tab2' as tabnam,b.* from table2 as  b
) as x
Group by the list of 20 columns
having count(distinct Tabnam) = 2

would find the duplicates  



ASKER CERTIFIED SOLUTION
Avatar of ramakrishnadasa
ramakrishnadasa

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial