Two possible solutions

😎

USE tempdb;

GO

SET NOCOUNT ON;

--- First Create two tables

IF OBJECT_ID(N'dbo.tabA') IS NOT NULL DROP TABLE dbo.tabA;

CREATE TABLE dbo.tabA

(

cola1 NVARCHAR(100) NULL

,cola2 NVARCHAR(100) NULL

,cola3 NVARCHAR(100) NULL

);

INSERT INTO dbo.tabA (cola1,cola2,cola3)

VALUES ( NULL, 'A1', 'A2')

,( 'A0', NULL, 'A2')

,( 'A0', 'A1', NULL)

,( 'A0', 'A1', 'A2')

IF OBJECT_ID(N'dbo.tabB') IS NOT NULL DROP TABLE dbo.tabB;

CREATE TABLE dbo.tabB

(

colb1 NVARCHAR(100) NOT NULL

,colb2 NVARCHAR(100) NOT NULL

,colb3 NVARCHAR(100) NOT NULL

,colb4 NVARCHAR(100) NOT NULL

);

INSERT INTO dbo.tabB (colb1,colb2,colb3,colb4)

VALUES ( 'B1', 'B2', 'B3', 'B4')

,( 'A0', 'A1', 'A2', 'THIS IS IT')

,( 'C9', 'B2', 'B3', 'B4')

-- IN THIS CASE THERE IS NO USE ADDING AN INDEX TO tabA, IT WILL NOT BE USED.

CREATE NONCLUSTERED INDEX NCLIDX_DBO_TABB_COLB1_COLB2_COLB3_INCL_COLB4 ON dbo.tabB (colb1 ASC,colb2 ASC,colb3 ASC) INCLUDE (colb4);

-- TWO METHODS

SELECT

TA.cola1

,TA.cola2

,TA.cola3

,TB.colb1

,TB.colb2

,TB.colb3

,TB.colb4

FROM dbo.tabA TA

INNER JOIN dbo.tabB TB

ON TB.colb1 = ISNULL(TA.cola1,TB.colb1)

AND TB.colb2 = ISNULL(TA.cola2,TB.colb2)

AND TB.colb3 = ISNULL(TA.cola3,TB.colb3)

;

----------------

SELECT

TA.cola1

,TA.cola2

,TA.cola3

,TB.colb1

,TB.colb2

,TB.colb3

,TB.colb4

FROM dbo.tabA TA

CROSS APPLY dbo.tabB TB

WHERE CASE WHEN TA.cola1 IS NULL THEN TB.colb1 ELSE TA.cola1 END = TB.colb1

AND CASE WHEN TA.cola2 IS NULL THEN TB.colb2 ELSE TA.cola2 END = TB.colb2

AND CASE WHEN TA.cola3 IS NULL THEN TB.colb3 ELSE TA.cola3 END = TB.colb3

;

Results

cola1 cola2 cola3 colb1 colb2 colb3 colb4

------ ------ ------ ------ ------ ------ -----------

NULL A1 A2 A0 A1 A2 THIS IS IT

A0 NULL A2 A0 A1 A2 THIS IS IT

A0 A1 NULL A0 A1 A2 THIS IS IT

A0 A1 A2 A0 A1 A2 THIS IS IT