CODEWITHSUNDEEP
postgresqlsubquerypostgresql-10in-subquery
codebot
codebot

Reputation: 697

PostgreSQL : comparing two sets of results does not work

I have a table that contains 3 columns of ids, clothes, shoes, customers and relates them.

I have a query that works fine :

select clothes, shoes from table where customers = 101 (all clothes and shoes of customer 101). This returns 

clothes - shoes (SET A)
1          6
1          2
33         12
24         null

Another query that works fine :

select clothes ,shoes from table where customers in (select customers from table where clothes = 1 and customers <> 101 ) (all clothes and shoes of any other customer than 101, with specified clothes). This returns

shoes - clothes(SET B)
6          null
null         24
1            1
2            1 
12          null
null         26
14           null

Now I want to get all clothes and shoes from SET A that are not in SET B.

So (example) select from SET A where NOT IN SET B. This should return just clothes 33, right?

I try to convert this to a working query : 

select clothes, shoes from table where  customers = 101 
and
(clothes,shoes) not in 
 (   
   select clothes,shoes from
   table where customers in 
   (select  customers   from table where clothes = 1 and customers <> 101 ) 
 ) ;

I tried different syntaxes, but the above looks more logic.

Problem is I never get clothes 33, just an empty set.

How do I fix this? What goes wrong?

Thanks

Edit , here is the contents of the table 

id  shoes   customers   clothes
1    1      1           1
2    1      4           1
3    1      5           1
4    2      2           2
5    2      3           1
6    1      3           1
44   2      101         1
46   6      101         1
49   12     101         33
51   13     102 
52          101         24
59          107         51
60          107         24
62   23     108         51
63   23     108         2
93          124         25
95   6      125 
98          127         25
100  3      128 
103  24     131 
104  25     132 
105         102         28
106  10     102 
107  23     133 
108         4           26
109  6      4   
110         4           24
111  12     4   
112  14     4   
116         102         48
117         102         24
118         102         25
119         102         26
120         102         29
122         134         31

Upvotes: 2

Views: 3015

Answers (2)

Vancalar
Vancalar

Reputation: 973

For PostgreSQL null is undefined value, so You must get rid of potential nulls in your result:

select id,clothes,shoes from t1 where  customers = 101 -- or select id... 
and (
 clothes  not in 
 (   
   select COALESCE(clothes,-1) from
   t1 where customers in 
   (select  customers   from t1 where clothes = 1 and customers <> 101 ) 
 ) 
OR 
 shoes not in 
 (   
   select COALESCE(shoes,-1) from
   t1 where customers in 
   (select  customers   from t1 where clothes = 1 and customers <> 101 ) 
  )
 )

if You wanted unique pairs you would use: 

select clothes, shoes from t1 where  customers = 101 
and
(clothes,shoes)  not in 
 (   
   select coalesce(clothes,-1),coalesce(shoes,-1) from
   t1 where customers in 
   (select  customers   from t1 where clothes = 1 and customers <> 101 ) 
 ) ;

You can't get "clothes 33" if You are selecting both clothes and shoes columns...

Also if u need to know exactly which column, clothes or shoes was unique to this customer, You might use this little "hack":

select id,clothes,-1 AS shoes from t1 where  customers = 101 
and 
 clothes  not in 
 (   
   select COALESCE(clothes,-1) from
   t1 where customers in 
   (select  customers   from t1 where clothes = 1 and customers <> 101) 
 )  
 UNION
  select id,-1,shoes from t1 where  customers = 101 
   and 
   shoes not in 
   (   
    select COALESCE(shoes,-1) from
    t1 where customers in 
    (select  customers   from t1 where clothes = 1 and customers <> 101) 
   )

And Your result would be: 

id=49, clothes=33, shoes=-1

(I assume that there aren't any clothes or shoes with id -1, You may put any exotic value here)

Cheers

Upvotes: 1

Hambone
Hambone

Reputation: 16387

The except clause in PostgreSQL works the way the minus operator does in Oracle. I think that will give you what you want.

I think notionally your query looks right, but I suspect those pesky nulls are impacting your results. Just like a null is not-NOT equal to 5 (it's nothing, therefore it's neither equal to nor not equal to anything), a null is also not-NOT "in" anything...

select clothes, shoes
from table1
where customers = 101

except  

select clothes, shoes
from table1
where customers in (
  select customers
  from table1
  where clothes = 1 and customers != 101
)

Upvotes: 1

Related Questions