SQL Query to perform a "reverse exclusion" on a LEFT JOIN with a select from the left side table I have a set of tables that I want to execute a LEFT JOIN on and bring back &quot;excluded&quot; rows.  In addition, I would like the left table's ID included as part of the results set.  Here's some sample data:




Table1_ID




1


3


5







Table2_ID
Table2_Val




1
a


2
b


3
c


4
d


5
c




A simple left join would look like this
SELECT t1.Table1_ID, t2.Table2_Val 
FROM Table2 t2 
LEFT JOIN Table1 t1 ON t2.Table2_ID = t1.Table1_ID 
WHERE t1.Table1_ID IS NULL

And yields the following table




Table1_ID
Table2_Val




NULL
b


NULL
d




However, my desired result is to have for each ID in Table2 that is missing in Table1, return the set of values in Table2 paired with the existing values in table 1.  Like so:




Table1_ID
Table2_Val




1
b


1
d


3
b


3
d


5
b


5
d




So far, the best I've been able to come up with is through using a CROSS JOIN to accomplish this, but I'd like to know if there's a better/more efficient way to handle this.  Here's the code I'm curious about improving:
WITH cte (T1_ID, T2_ID, T2_Value)
AS
(
SELECT t1.Table1_ID, t2.Table2_ID, t2.Table2_Val 
FROM Table1 t1 CROSS JOIN Table2 t2
GROUP BY t1.Table1_ID, t2.Table2_ID, t2.Table2_Val
)
SELECT
  cte.Table1_ID,
  t2.Table2_Val
FROM Table2 t2
INNER JOIN cte ON t2.Table2_ID = cte.T2_ID
LEFT JOIN Table1 t1 ON t2.Table2_ID = t1.Table1_ID
WHERE t1.Table1_ID IS NULL
You already have the table2_vals you need.
Just cross join them with table1. No need for long nested stuff and group by:
select t1x.Table1_Id,q.Table2_Val
from
    Table1 t1x
    cross join
    (
        SELECT t2.Table2_Val 
        FROM Table2 t2 
        LEFT JOIN Table1 t1 ON t2.Table2_ID = t1.Table1_ID 
        WHERE t1.Table1_ID IS NULL
    )q