patternMinor
Check if all record columns of 2 Tables/Query are equal
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:
Using
Edit: Order is irrelevant
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 falsedeclare @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 falsedeclare @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 trueUsing
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.