-1

Joins always gets me :/ so far what I have is 

SELECT  *
FROM    
    #Table1 aa
RIGHT OUTER JOIN
    #Table2 bb
ON  a.FieldA = b.FieldB
GMB
  • 216,147
  • 25
  • 84
  • 135
Big_Dwarf90
  • 77
  • 7
  • 1
    Please read over [how to ask](https://stackoverflow.com/help/how-to-ask) and edit your question with a more complete example. In the meantime, `SELECT * FROM a LEFT JOIN b ON b.FieldB = a.FieldA WHERE a.OtherField IS NULL` should get you close. – WOUNDEDStevenJones May 21 '20 at 21:43
  • This is a faq. Before considering posting please read your textbook and/or manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. If you post a question, use one phrasing as title. Reflect your research. See [ask] & the voting arrow mouseover texts. – philipxy May 21 '20 at 22:17

1 Answers1

1

I would recommmend not exists:

select t2.*
from #table2 t2
where not exists (select 1 from #table1 t1 where t1.fieldA = t2.fieldB)

This seems like the most straight-forward way to express what you want, and should have good performance, provided that you have indexes on #table2(fieldB) and #table1(fieldA).

If you really want to do this with a join, then you can use an anti-left join, like so:

select t2.*
from #table2 t2
left join #table1 t1 on t1.fieldA = t2.fieldB
and t1.fieldA is null

The logic is to attempt to join both tables, and pull out records of #table2 for which the join did not succeed.

GMB
  • 216,147
  • 25
  • 84
  • 135