HiveBrain v1.2.0
Get Started
← Back to all entries
patternMinor

Check if all record columns of 2 Tables/Query are equal

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
tablescolumnsallareequalqueryrecordcheck

Problem

In SQL Server 2005, I would like to make one query that checks if all record columns of 2 tables are the same,
Example:

declare @tbl1 table(col nvarchar(50))
declare @tbl2 table(col nvarchar(50))

insert into @tbl1
    select '11' union select '22'
insert into @tbl2
    select '22' 
--should return false


declare @tbl1 table(col nvarchar(50))
declare @tbl2 table(col nvarchar(50))

insert into @tbl1
    select '11' 
insert into @tbl2
    select '11' union select '22'
--should return false


declare @tbl1 table(col nvarchar(50))
declare @tbl2 table(col nvarchar(50))

insert into @tbl1
    select '11' union select '22'
insert into @tbl2
    select '11' union select '22'
--should return true


Using IN clause, or LEFT JOIN i'm only able to check if all record columns in one of the tables are present on the other.

Edit: Order is irrelevant

Solution

I think this will work:

SELECT CASE WHEN NOT EXISTS
         ( SELECT * FROM table1
           EXCEPT 
           SELECT * FROM table2
         ) 
             AND NOT EXISTS 
         ( SELECT * FROM table2
           EXCEPT 
           SELECT * FROM table1
         ) 
         THEN 'True' ELSE 'False'
       END AS result ;

Code Snippets

SELECT CASE WHEN NOT EXISTS
         ( SELECT * FROM table1
           EXCEPT 
           SELECT * FROM table2
         ) 
             AND NOT EXISTS 
         ( SELECT * FROM table2
           EXCEPT 
           SELECT * FROM table1
         ) 
         THEN 'True' ELSE 'False'
       END AS result ;

Context

StackExchange Database Administrators Q#65052, answer score: 5

Revisions (0)

No revisions yet.