1

Topic: Determination of mismatches of records

All greetings.
There are two tables with identifiers Table1 and Table2.
In them the miscellaneous at lines is constant and I need to compare them.
Help to understand as fastest to deduce the identifier list.

SELECT id
FROM Table1
WHERE id not in
(SELECT id FROM Table2)

Or all the same

SELECT id
FROM Table1
WHERE not EXISTS
(SELECT id FROM Table2
WHERE ID =
Table1.id)

According to plan of request identical values, and on runtime that one another "benefits".

2

Re: Determination of mismatches of records

SELECT [T1.id] =T1.id. [T2.id] =T2.id
FROM Table1 T1
FULL JOIN Table2 T2 ON T1.id=T2.id
WHERE T1.id IS NULL OR T2.id IS NULL;

3

Re: Determination of mismatches of records

iap;
Perhaps, I incorrectly expressed:
I always compare Table1 with Table2 and, in case of appearance new id (which else is not present in Table2), I fulfill with them procedure.
And to compare some millions lines is a minute-two in my case...

4

Re: Determination of mismatches of records

Aleksey br wrote:

iap;
Perhaps, I incorrectly expressed:
I always compare Table1 with Table2 and, in case of appearance new id (which else is not present in Table2), I fulfill with them procedure.
And to compare some millions lines is a minute-two in my case...

Also what? I offered wrong request?
The answer to an initial question: NOT EXISTS always it is better, than NOT IN.
Including because of potential possibility of reset NULL in a subquery.
In this case, truth, it is eliminated, time is a question about id.

5

Re: Determination of mismatches of records

By my experience not in it is frequent . Besides the logical operator in this subset exists, and, basically, is superfluous. Is hardly shorter unless

6

Re: Determination of mismatches of records

dies irae wrote:

by my experience not in it is frequent . Besides the logical operator in this subset exists, and, basically, is superfluous. Is hardly shorter unless

It not , and at it other logic on handling UNKNOWN. But  without reflecting better NOT EXISTS to use

7

Re: Determination of mismatches of records

And a variant

SELECT id
FROM Table1
LEFT JOIN Table2 ON Table1.id = Table2.id
WHERE Table2.id IS NULL

You at all do not consider? And why?

8

Re: Determination of mismatches of records

Akina;
Considered - the result not especially differs

9

Re: Determination of mismatches of records

Aleksey br, compare still with

SELECT id
FROM Table1
EXCEPT
SELECT id
FROM Table2

At me it shows identical results c not exists, so this matter of taste, as a whole. It is not necessary to use only not in.

10

Re: Determination of mismatches of records

Aleksey br wrote:

Perhaps, I incorrectly expressed:
I always compare Table1 with Table2 and, in case of appearance new id (which else is not present in Table2), I fulfill with them procedure.
And to compare some millions lines is a minute-two in my case...

Whence to the server the nobility about new lines, "which else is not present in Table2"? Therefore you are doomed to compare some millions lines, yet do not teach the server to find the necessary at once.
For example, if  new id in Table1 always more previous it is possible approximately so

SELECT id
FROM Table1
WHERE id> isnull ((SELECT MAX (id) FROM Table2),-1)

Or use kakm-or - or the regular mechanism. For example, Change Tracking

11

Re: Determination of mismatches of records

You solve the HARDWARE, and which task?
Can dance from it? Here while it is clear that you solved that it is necessary to compare   sets.
But it is not eliminated that the primordial task not to compare, and any another. And your decision only one of the possible...