Opposite Of An Inner Join Query

Table 1 2 columns: ID, Name

Table 2 2 columns: ID, Name

What is a query to sho开发者_运维技巧w names from Table 1 that are not in table 2? So filtering out all the names in table 1 that are in table 2 gives the result query. Filtering is on ID not name.

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

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)

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.

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.

Try like this:

select t1.*
from table1 as t1
where t1.id not in 
  (select distinct t2.id from table2 as t2);