INNER JOIN ON NULLABLE FIELDS - scrappyCoco/SQL-Note GitHub Wiki

Подготовка исходных данных:

CREATE TABLE SomeData (
  SomeDataId INT         NOT NULL IDENTITY (1, 1),
  Key1       VARCHAR(10) NULL,
  Key2       INT         NULL,
  CONSTRAINT PK_dbo_SomeData PRIMARY KEY (SomeDataId),
  CONSTRAINT UN_dbo_SomeData UNIQUE (Key1, Key2)
);

GO ;

INSERT INTO SomeData (Key1, Key2)
VALUES
  ('1111', 1111),
  ('2222', NULL),
  (NULL, 3333);

GO ;

Запрос:

DECLARE @table TABLE(
  Key1 VARCHAR(10) NULL,
  Key2 INT         NULL
);

INSERT INTO @table (Key1, Key2)
VALUES
  ('2222', NULL);

-- OLD SCHOOL.
SELECT *
FROM @table as TempTable
INNER JOIN SomeData ON (SomeData.Key1 = TempTable.Key1 OR SomeData.Key1 IS NULL AND TempTable.Key1 IS NULL)
                       AND (SomeData.Key2 = TempTable.Key2 OR SomeData.Key2 IS NULL AND TempTable.Key2 IS NULL);

-- NEW SCHOOL.
SELECT *
FROM @table as TempTable
INNER JOIN SomeData ON EXISTS(
        SELECT
          SomeData.Key1,
          SomeData.Key2
        INTERSECT
        SELECT
          TempTable.Key1,
          TempTable.Key2
        );