0
\$\begingroup\$

I have a set of tables that I want to execute a LEFT JOIN on and bring back "excluded" 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
\$\endgroup\$
2
  • \$\begingroup\$ Is it not efficient? \$\endgroup\$ Dec 23, 2020 at 21:33
  • \$\begingroup\$ Right now it performs well enough to be usable, but the CROSS JOIN has me worried about scaling this solution. \$\endgroup\$
    – akousmata
    Dec 30, 2020 at 16:17

1 Answer 1

1
\$\begingroup\$

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
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.