In general, there are 2 tables that I want to join by 2 fields.

Request example:

SELECT * FROM T1 LEFT JOIN T2 ON T1.F=T2.F AND T1.F2=T2.F2 WHERE T2.ID IS NULL

So, this request returns nothing to me at all, although there are such records.

By scientific method, I came to the conclusion that this is due to the fact that the fields by which I make the connection can be NULL .

If I rewrite the query like this:

  SELECT * FROM T1 LEFT JOIN (SELECT * FROM T2 WHERE T2.F1 IS NOT NULL AND T2.F2 IS NOT NULL) AS T2 ON T1.F=T2.F AND T1.F2=T2.F2 WHERE T2.ID IS NULL

That returns the expected result. Prompt, it should work this way, or can the first query be rewritten without a subquery, so that everything is clear?

  • The first request will not return any records only if each record from T1 corresponds to at least one record from T2 in the corresponding fields. Do you have exactly LEFT, not INNER? When Left join, NULL shouldn't affect it like this: sqlfiddle.com/#!18/d9226/2 - Zufir
  • I came to the conclusion that this is due to the fact that the fields for which I produce the connection can be NULL Which of the tables? As an option - demonstrate this in the question text with an example of the initial data and results (obtained and desired). - Akina
  • Some kind of crap. Now everything works. Maybe I blunted late night ... = (Or maybe MS SQL on a large amount of data performs some optimization => I see no problems when I try to repeat the behavior on 5 test records. - iluxa1810

0