Opposite Of An Inner Join Query

Question

Table 1 2 columns: ID, Name

Table 2 2 columns: ID, Name

What is a query to show 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.

Nick, all of the suggestions below (those using a join and checking for null and those that are suggest using a NOT IN clause) will work. However, if speed is an issue, I believe that NOT IN is slower. If speed isn't an issue, then the NOT IN is probably clearer. — Matt, Oct 28 '10 at 16:23
The opposite of an INNER JOIN is an OUTER JOIN and it comes in two flavors: LEFT and RIGHT depending on which side of of the JOIN you want to "outer" — Paul Sasik, Oct 28 '10 at 16:23
You are quite right @Quassnoi. It took me a while to get the time and a dataset large enough to measure it, but even the query execution plan shows that NOT IN is about 50% faster. I'm not sure who told me that dirty lie in the first place! :) — Matt, Nov 02 '10 at 17:44

score 79 · Accepted Answer · answered Oct 28 '10 at 16:20

79

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

answered Oct 28 '10 at 16:20

Andrew

26,629
5
63
86

7

NOT EXISTS is much faster. https://github.com/ebergstedt/sql_performance_left_outer_join_null_vs_not_exists – Erik Bergstedt Mar 11 '16 at 11:53

Joe Stefanelli · Answer 2 · 2022-11-11T15:56:48.923

39

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)

edited Nov 11 '22 at 15:56

answered Oct 28 '10 at 16:31

Joe Stefanelli

132,803
19
237
235

@Eduardo Cuomo: Not in SQL Server. – Joe Stefanelli Mar 12 '14 at 13:11

score 19 · Answer 3 · answered Oct 28 '10 at 16:19

19

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.

answered Oct 28 '10 at 16:19

DForck42

19,789
13
59
84

score 3 · Answer 4 · edited Jul 02 '15 at 16:40

3

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.

edited Jul 02 '15 at 16:40

i_saw_drones

3,486
1
31
50

answered Oct 28 '10 at 16:19

Matt

562
1
4
8

score 1 · Answer 5 · edited Aug 06 '21 at 09:21

1

Try like this:

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

edited Aug 06 '21 at 09:21

Dominique

16,450
15
56
112

answered Aug 04 '21 at 14:13

stanieviv

11
1

Opposite Of An Inner Join Query

5 Answers5