CODEWITHSUNDEEP
sqlpostgresql
John
John

Reputation: 1852

PostgreSQL: how to use NOT IN without WHERE?

I have two queries:

select * from  tableA

and

select a,b from tableA
group by a,b

the first query returns 2101 rows the second query returns 2100 rows

I want to know which row is in the first but not in the second. It should be simple with NOT IN, but I can't find the correct syntax as NOT IN should be in WHERE statement. but I don't have a WHERE statement in my case.

Upvotes: 0

Views: 114

Answers (2)

Cetin Basoz
Cetin Basoz

Reputation: 23867

There are N ways to do that and one of the simplest should be to find the rows that have a count > 1 when grouped on a,b.

select a,b from tableA
group by a,b
having count(*) > 1

Here is a sample:

with tableA as 
( 
   select * from (values 
     (1,1,1), 
     (1,1,1), 
     (1,2,1)
     ) as t(a,b,c)
)

select a, b from tableA
group by a, b
having count(*) > 1;

Upvotes: 1

Yurii K
Yurii K

Reputation: 162

You can get duplicates this way:

select a,b from tableA
group by a,b having count(1) > 1

Upvotes: 1

Related Questions