How do I join getting one row from the left table, no matter how many matches i get from the right table?

Question

I have two tables - one is a data table and the other is a mapping table. I want to join them together, but only preserve the data from the left table. However, it is possible that the mapping table may contain multiple records that match to a single record in the data table. I cannot use a DISTINCT because there may be identical rows in the data table, and I want to preserve the same number of rows from that table in the result set.

Here is a sample of the data I am working with:

       DataTable                    MappingTable                 Expected Result
+-----+-----+-----+-----+   +------+------+------+------+   +-----+-----+-----+-----+
| ID1 | ID2 | ID3 | ID1 |   | ID1  | ID2  | ID3  | ID1  |   | ID1 | ID2 | ID3 | ID1 |
+-----+-----+-----+-----+   +------+------+------+------+   +-----+-----+-----+-----+
|  1  |  1  |  1  |  1  |   |  1   | NULL | NULL | NULL |   |  1  |  1  |  1  |  1  |
|  1  |  1  |  1  |  1  |   | NULL | NULL | NULL |  1   |   |  1  |  1  |  1  |  1  |
|  2  |  1  |  1  |  1  |   |  3   |  3   | NULL | NULL |   |  2  |  1  |  1  |  1  |
|  3  |  1  |  1  |  3  |   +------+------+------+------+   |  2  |  2  |  1  |  1  |
|  4  |  1  |  1  |  4  |                                   |  3  |  3  |  1  |  3  |
|  2  |  2  |  1  |  1  |                                   |  2  |  1  |  0  |  1  |
|  3  |  2  |  1  |  3  |                                   |  2  |  1  |  0  |  1  |
|  3  |  3  |  1  |  3  |                                   +-----+-----+-----+-----+
|  2  |  1  |  0  |  1  |
|  2  |  1  |  0  |  1  |
|  4  |  3  |  2  |  3  |
+-----+-----+-----+-----+

Below is the join I am using. I wrote a custom function to handle the NULL-matching behavior, which I am including here as well.

SELECT * FROM DataTable P
JOIN MappingTable M ON dbo.fNullMatchCheckIntS(P.ID1,M.ID1,0,1) = 1
    AND dbo.fNullMatchCheckIntS(P.ID2,M.ID2,0,1) = 1
    AND dbo.fNullMatchCheckIntS(P.ID3,M.ID3,0,1) = 1
    AND dbo.fNullMatchCheckIntS(P.ID4,M.ID4,0,1) = 1

---

CREATE FUNCTION dbo.fNullMatchCheckIntS (
    @Value1 INT
    ,@Value2 INT
    ,@AutoMatchIfValue1IsNull BIT
    ,@AutoMatchIfValue2IsNull BIT
)
    RETURNS BIT
AS

BEGIN

    DECLARE @Result BIT = 0

    SELECT
        @AutoMatchIfValue1IsNull = ISNULL(@AutoMatchIfValue1IsNull,0)
        ,@AutoMatchIfValue2IsNull = ISNULL(@AutoMatchIfValue2IsNull,0)

    IF
        (@AutoMatchIfValue1IsNull = 1 AND @Value1 IS NULL)
        OR (@AutoMatchIfValue2IsNull = 1 AND @Value2 IS NULL)
        OR @Value1 = @Value2
        OR (@Value1 IS NULL AND @Value2 IS NULL)
    BEGIN
        SET @Result = 1
    END

    RETURN @Result
END

The problem with the way the join works is that the first two rows in the DataTable match on the first two rows in the MappingTable, giving me four identical records in the result, but I only want 2. I know that I could add an identity column to the DataTable and then use DISTINCT or PARTITION to get the result I am looking for, but I would like to avoid that route if possible.

EDIT: I figured out a way to do this using EXISTS, but it looks a little ugly in my opinion. Still interested in other answers if anyone has an idea. Thanks!

SELECT * FROM DataTable D
WHERE EXISTS (
    SELECT D.ID1, D.ID2, D.ID3, D.ID4
    FROM MappingTable M 
    WHERE dbo.fNullMatchCheckIntS(D.ID1,M.ID1,0,1) = 1
        AND dbo.fNullMatchCheckIntS(D.ID2,M.ID2,0,1) = 1
        AND dbo.fNullMatchCheckIntS(D.ID3,M.ID3,0,1) = 1
        AND dbo.fNullMatchCheckIntS(D.ID4,M.ID4,0,1) = 1
)

Answer 1

You need a semi join so EXISTS is the right way.

Scalar UDFs are well known performance killers in SQL Server and not needed here. I'd probably do something like

SELECT * 
FROM DataTable D
WHERE EXISTS (
   SELECT D.ID1, D.ID2, D.ID3, D.ID4
   INTERSECT
  SELECT ISNULL(M.ID1, D.ID1),
         ISNULL(M.ID2, D.ID2),
         ISNULL(M.ID3, D.ID3),
         ISNULL(M.ID4, D.ID4)
  FROM MappingTable M
)

This does have the same disadvantage as the code in your original question that it will, in the worst case, need to do a cross join between the two tables and evaluate the predicate on every row (though the semi join can stop once the first matching row is found for each outer row). If the mapping table is large and has appropriate indexes to allow a seek you could potentially calculate the 16 possible matching rows per DataTable row and seek into them directly with something like the below

SELECT * 
FROM DataTable D
WHERE EXISTS (
    SELECT V1.ID1, V2.ID2, V3.ID3, V4.ID4
     FROM (VALUES(D.ID1),(NULL)) V1(ID1),
          (VALUES(D.ID2),(NULL)) V2(ID2),
          (VALUES(D.ID3),(NULL)) V3(ID3),
          (VALUES(D.ID4),(NULL)) V4(ID4)
   INTERSECT
  SELECT M.ID1, M.ID2, M.ID3, M.ID4
  FROM MappingTable M
)

NB: For more about the INTERSECT part you can see here

Answer 2

DBFIDDLE

SELECT 
    d.id1, d.id2, d.id3, d.id4
FROM (SELECT ROW_NUMBER() over (ORDER by d1.id1,d1.id2,d1.id3,d1.id4) as r, 
             d1.* 
      FROM DataTable d1) d
INNER JOIN MappingTable m on 
    (m.id1=d.id1 or m.id1 is null) and 
    (m.id2=d.id2 or m.id2 is null) and
    (m.id3=d.id3 or m.id3 is null) and
    (m.id4=d.id4 or m.id4 is null)
group by d.r,d.id1,d.id2,d.id3,d.id4;

output:

id1         id2         id3         id4
----------- ----------- ----------- -----------
1           1           1           1
1           1           1           1
2           1           0           1
2           1           0           1
2           1           1           1
2           2           1           1
3           3           1           3