Opposite Of An Inner Join Query

Question

can someone help me write sql for a scernerio like this:

Table 1

2 columns: ID, Name

Table 2

2 columns: ID, Name

I want a query to show names from Table 1 that are not in table 2. So filter out all the names in table 1 that are in table 2 is the result query. Use ID for the filtering not name.

This will help me in what I am trying to do. Thanks in advance

Answer 1

Use this query

select
t1.*
from table1 t1
left outer join table2 t2
on t1.id=t2.id
where t2.id is null

this works by joining everything in t1 to whatever exists in t2. the where clause filters out all of the records that don't exist in t2.

Answer 2

SELECT Table1.ID, Table1.Name, Table2.ID FROM Table1 LEFT OUTER JOIN Table2 ON Table1.ID = Table2.ID WHERE Table2.ID IS NULL

I think that should do it.

Answer 3

Select * from table1
left join table2 on table1.id = table2.id
where table2.id is null

Answer 4

This should perform better than the left join...is null version. See here and here for comparisons.

select t1.id, t1.name
    from table1 t1
    where not exists(select null from table2 t2 where t2.id = t1.id)