CODEWITHSUNDEEP
sqlsql-server-2005
user974047
user974047

Reputation: 2295

Find where a row of data in one column doesn't exist in another column

so I'm trying to figure out a way to compare each and every row of a column against all the rows in another column and filter out those that doesn't exist in the column that it is comparing to. I understand how to compare the two columns on a row-by-row basic like this:

select table1.column1
from table1 inner join
     table2 on table1.id = table2.id
where column1 <> column2

But I want to compare the rows from table1 in column1 against ALL the rows of column2 in table2 and find rows in column1 that doesn't exist in column2 at all. So it would be something like columns with these values:

Column1        Column2
1              2
2              1
4              3
5              5
7              6

And after the SQL it would become something like this:

Column1
4
7

Upvotes: 0

Views: 2876

Answers (4)

Dumitrescu Bogdan
Dumitrescu Bogdan

Reputation: 7267

select t1.id
from ... t1
left join ... t2 on t1.id = t2.id
where t2.id is null

Upvotes: 0

Chanon
Chanon

Reputation: 476

If you compare column between 2 tables

SELECT table1.column1 FROM table1
WHERE table1.column1 NOT IN
(SELECT table2.column2 as column1 FROM table2)

If you compare column1 with column2 against same table

SELECT table1.column1 FROM table1
WHERE table1.column1 NOT IN
(SELECT table1.column2 as column1 FROM table1)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271111

Try with NOT IN:

select table1.column1
from table1
where table1.column1 not in (select table2.column2 from table2)

Upvotes: 1

Madhivanan
Madhivanan

Reputation: 13700

select column1 from table1 as t1 where not exists
(select column2 from table2 as t2 where t1.id=t2.id)

Upvotes: 0

Related Questions