How to put nested query in a JOIN?

Question

Let's say that I have the following two tables:

TABLE1

+-------+-------+-------+
| data1 | data2 | data3 |
+-------+-------+-------+
|     1 |    12 |    13 |
|     2 |    22 |    23 |
|     3 |    32 |    33 |
+-------+-------+-------+

TABLE2

+-------+-------+-------+
| data1 | data4 | data5 |
+-------+-------+-------+
|     1 |  NULL |   015 |
|     1 |    14 |   115 |
|     1 |    14 |   115 |
|     2 |  NULL |   025 |
|     2 |    24 |   125 |
|     2 |    24 |   125 |
|     3 |  NULL |   035 |
|     3 |    34 |   135 |
|     3 |    34 |   135 |
+-------+-------+-------+

And I have the following query:

SELECT TABLE1.data1,
       TABLE1.data2,
       TABLE1.data3,
       (SELECT TOP 1
               data4
        FROM TABLE2
        WHERE data1 = TABLE1.data1
          AND data4 IS NOT NULL),
       (SELECT TOP 1
               data5
        FROM TABLE2
        WHERE data1 = TABLE1.data1
          AND data4 IS NOT NULL)
FROM TABLE1;

QUERY RESULT

+-------+-------+-------+-------+-------+
| data1 | data2 | data3 | data4 | data5 |
+-------+-------+-------+-------+-------+
|     1 |    12 |    13 |    14 |   115 |
|     2 |    22 |    23 |    24 |   125 |
|     3 |    32 |    33 |    34 |   135 |
+-------+-------+-------+-------+-------+

Assuming the TABLE2 meets these two conditions:

1. Foreach data1, data4 can either be 1 or have the same value in every row.
2. Foreach data1, data5 will have one value for each row with data4 null and another for each row with data4 not null.

Is there a way to rewrite the query in such a way that I don't have a nested query in the select part? Maybe using JOIN statements? I'm asking because I've realized that the performance of the nested query in the SELECT is quite poor. However, if I try with a JOIN I end up duplicating the rows that have data4 different than null.

Answer 1

You can use OUTER APPLY or CROSS APPLY

SELECT TABLE1.data1,
       TABLE1.data2,
       TABLE1.data3,
       t2.data4,
       t2.data5
FROM TABLE1
OUTER APPLY (SELECT TOP 1
               data4,
               data5
        FROM TABLE2 t2
        WHERE t2.data1 = TABLE1.data1
          AND t2.data4 IS NOT NULL
        ORDER BY t2.SomeColumn
-- TOP should have an ORDER BY otherwise results are not guaranteed
) t2;