CODEWITHSUNDEEP
sqlt-sql
Rustam Salakhutdinov
Rustam Salakhutdinov

Reputation: 804

How can check equality of 2 tables by specified id fields?

I have 2 tables like this:

CREATE TABLE [dbo].[M_FirstTable](
-- ...
[SomeId] [bigint] NOT NULL,
-- ...
)

CREATE TABLE [dbo].[M_SecondTable](
-- ...
[SomeId] [bigint] NOT NULL,
-- ...
)

How can I check: is this two table equal by all SomeId field of their items?

For example: if FirstTable = {{SomeId = 1}, {SomeId = 2}} and SecondTable = {{SomeId = 1}}, then this two tables are not eqaul, because there is not element with SomeId = 2 in SecondTable.

Upvotes: 0

Views: 50

Answers (2)

Randy Minder
Randy Minder

Reputation: 48432

Maybe something like this:

Select SomeId From FirstTable

Except

Select SomeId From SecondTable

Union All

Select SomeId From SecondTable

Except

Select SomeId From FirstTable

This will generate a resultset of all rows in FirstTable that are not in SecondTable and all rows in the SecondTable that are not in FirstTable.

Upvotes: 1

Deep
Deep

Reputation: 3202

This might be a useful query too :

SELECT a.[SomeId],
       b.[SomeId]
FROM   M_FirstTable a
       FULL OUTER JOIN M_SecondTable b
                    ON a.[SomeId] = b.[SomeId]
WHERE  a.[SomeId] IS NULL
        OR b.[SomeId] IS NULL

1. when a.[SomeId] is not null then it is present only in first table.

  1. when b.[SomeId] is not null then it is present only in second table.

Upvotes: 3

Related Questions