How to select rows without appropriate keys?

Question

Here are 2 tables:

declare @Table1 table (ID int NOT NULL PRIMARY KEY, Value int)
declare @Table2 table (ID int NOT NULL PRIMARY KEY, Value int)

insert into @Table1 (ID, Value)
select 1, 100
union all
select 2, 101
union all
select 3, 103
union all
select 4, 104
union all
select 5, 105

insert into @Table2 (ID, Value)
select 1, 100
union all
select 2, 110
union all
select 3, 111

I need to select all rows from first table, which Value's values are not in Table2. How to do it?

score 0 · Accepted Answer · answered Nov 26 '15 at 08:15

0

Something like this?

Select * from Table1 where value not in(select distinct value from table2)

answered Nov 26 '15 at 08:15

Nemeas

129
1
10

What if some value in second table is NULL? – tesicg Nov 26 '15 at 08:25
It's not new question, because the upper query doesn't work if Value's value is NULL. – tesicg Nov 26 '15 at 08:48
1

This post might help: [link](http://stackoverflow.com/questions/2686254/how-to-select-all-records-from-one-table-that-do-not-exist-in-another-table) – Nemeas Nov 26 '15 at 08:55

score 0 · Answer 2 · answered Nov 27 '15 at 09:03

0

SELECT t1.* FROM @Table1 t1 LEFT JOIN @Table2 t2 ON t2.value = t1.value WHERE t2.value IS NULL

answered Nov 27 '15 at 09:03

DJs

176
1
2
11

How to select rows without appropriate keys?

2 Answers2