Let's suppose I have a table with integer columns a and b (each pair (a,b) is unique) and following data:

 a | b
 1 | 2
 1 | 3
 1 | 4
 1 | 5
 2 | 1
 2 | 3
 2 | 4
 3 | 1
 3 | 3

Now I need to get all the rows whose b value is not contained in the set with some other a value (i.e. they appear just once in the union of rows with the 'a' values in question). From these I should get (1,2), (1,5) and (2,1).

I want to include only two sets (groups with the same a value; I want to use this as part of a function, where both a values are given as parameters) at a time, so I don't want to get (3,1) from the new data.

I have some (working) solution; I post it as an answer. However, there should be something more elegant - the query should be one and exploit the symmetricity, not two unioned queries. I thought of various solutions listed in this answer, but after some hour of playing with code I didn't find how to make anything better and working.

  • Is (a,b) defined UNIQUE? Primary key? or can there be dupes? And please clarify the ambiguous question not contained in the set with different a value. "different" is ambiguous. And how exactly do you pick the "two sets" to restrict the results to? And why restrict it? Commented Aug 20, 2014 at 15:40
  • @ErwinBrandstetter: I hope it is better now.
    – Pavel V.
    Commented Aug 20, 2014 at 17:32
  • Much better now. Commented Aug 20, 2014 at 17:38

5 Answers 5


If I interpret your question correctly:

"Pick all rows where a has one of two given values, and b only exists in combination with one of them."

You can use any of the standard techniques laid out in the referenced answer.

Just restrict your base table to the two given a. For instance

FROM   tbl t1
WHERE  a IN (1,2)
   FROM   tbl t2
   WHERE  a IN (1,2)
   AND    t2.b =  t1.b
   AND    t2.a <> t1.a

SQL Fiddle

This also works with duplicates on (a,b). Multiple identical rows would be returned.

  • My current favourite - performancewise it seems similar to Craig Ringer's answer, and I like this one because it means very few code changing for me. +1
    – Pavel V.
    Commented Aug 20, 2014 at 17:34

If your pairs are unique, as in the example you gave, you want the values of b that appear only once, and this would be a solution:

FROM tbl1
WHERE b in (
  FROM tbl1
  HAVING (count(b)) = 1

You can see it in this fiddle http://sqlfiddle.com/#!15/c82d3/3

  • +1 - this perfectly answers the question as worded before I realized I didn't finish an important sentence. Add a note (or add another query, if it is too different) on how to constraint the second select to a=2 and I will accept it if it prove to be better than Craig Ringer's answer.
    – Pavel V.
    Commented Aug 20, 2014 at 13:39
  • Meanwhile a correct answer that meets the new requirement has been given, but just for the record, it can be adapted to the new requirements by adding a where clause to both selects that filters the a values. Commented Aug 21, 2014 at 9:23

I think this solution is pleasingly simple:

SELECT coalesce(a1.a, a2.a) AS a, coalesce(a1.b, a2.b) AS b
  SELECT a, b FROM tbl1 WHERE a = 1
) a1
  SELECT a, b FROM tbl1 WHERE a = 2
) a2 
ON (a1.b = a2.b)
WHERE a1.a IS NULL or a2.a IS NULL;

What it does: self-join tbl1, filtering it into two distinct sets, a1 with values a=2, and a2 with values a=2. Do a full outer join as the self join - this is a join type that's like a simultaneous left and right join, i.e. nulls can appear on either side if there's no match. Then find rows where one side or the other is null, and return the values of the non-null side.

In other words: Find sets a=1 and a=2 then do a full anti-join and coalesce the results.

The plan looks promising too:

                                        QUERY PLAN                                        
 Hash Full Join  (cost=8.29..16.33 rows=1 width=16)
   Hash Cond: (tbl1.b = tbl1_1.b)
   Filter: ((tbl1.a IS NULL) OR (tbl1_1.a IS NULL))
   ->  Index Scan using tbl1_a_idx on tbl1  (cost=0.13..8.15 rows=1 width=8)
         Index Cond: (a = 1)
   ->  Hash  (cost=8.15..8.15 rows=1 width=8)
         ->  Index Scan using tbl1_a_idx on tbl1 tbl1_1  (cost=0.13..8.15 rows=1 width=8)
               Index Cond: (a = 2)
(8 rows)


Unfortunately I don't think the approach can be generalized for 'n' sets using a recursive CTE, as you can't outer-join on the recursive term, at least in PostgreSQL. (Constantly annoys me).

  • Thanks! I already tried the FULL JOIN solution, but didn't manage to do it right. +1 and hot candidate for accept.
    – Pavel V.
    Commented Aug 20, 2014 at 13:44
  • @PavelV. You probably tried to use an outer-level WHERE on the two relations, but that's too late. So is doing it within the ON(...) join condition. Commented Aug 20, 2014 at 13:45

My original solution:

  SELECT a, b 
  FROM tbl t1
    SELECT 1 FROM tbl t2
    WHERE t2.a = 2 AND t2.b = t1.b
  SELECT a, b 
  FROM tbl t1
    SELECT 1 FROM tbl t2
    WHERE t2.a = 1 AND t2.b = t1.b
  • 1
    @a_horse_with_no_name: the consensus on meta.SE tells otherwise (for example here ), so I undelete this answer and rollback the edit.
    – Pavel V.
    Commented Aug 20, 2014 at 13:10
  • If this is a possible answer, it's fine as it is. If it's (not completely) working as intended, it should go into the question. Commented Aug 20, 2014 at 15:27
  • @ErwinBrandstetter: it is a possible answer, though some others are better.
    – Pavel V.
    Commented Aug 20, 2014 at 17:09
  • Then you did it right, I'd say. Commented Aug 20, 2014 at 17:15

I haven't run this so please forgive the odd typo:

with Table1 as
    select b from tbl where a = 1
, Table2 as
    select b from tbl where a = 2
, Added as
    select * from Table1
    select * from Table2
, Subtracted as
    select * from Table1
    select * from Table2
, Unmatched as
    select * from Added
    select * from Subtracted
from Unmatched as u
inner join tbl as t
    on t.b = u.b
    and t.a in (1,2);

I'm guessing it will run dog slow, however, with all those table scans.

Fixed after Erwin's comment; removed a from the CTE and joined to the original tbl to get the output.

  • Dog slow would be ok, for the sake of outstanding clarity. However, I don't think it works as intended - not as intended by the OP, anyway. I tested with my fiddle: sqlfiddle.com/#!15/efbc3/2 Commented Aug 20, 2014 at 22:16
  • Thanks @Erwin; see the amended answer for what I changed. Commented Aug 20, 2014 at 23:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.