Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
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? –  Erwin Brandstetter Aug 20 at 15:40
    
@ErwinBrandstetter: I hope it is better now. –  Pavel V. Aug 20 at 17:32
    
Much better now. –  Erwin Brandstetter Aug 20 at 17:38

5 Answers 5

up vote 2 down vote accepted

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

SELECT *
FROM   tbl t1
WHERE  a IN (1,2)
AND    NOT EXISTS (
   SELECT 1
   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.

share|improve this answer
    
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. Aug 20 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:

SELECT a,b
FROM tbl1
WHERE b in (
  SELECT b
  FROM tbl1
  GROUP BY b
  HAVING (count(b)) = 1
  )

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

share|improve this answer
    
+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. Aug 20 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. –  user16484 Aug 21 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
FROM (
  SELECT a, b FROM tbl1 WHERE a = 1
) a1
FULL OUTER JOIN (
  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)

SQLFiddle.

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).

share|improve this answer
    
Thanks! I already tried the FULL JOIN solution, but didn't manage to do it right. +1 and hot candidate for accept. –  Pavel V. Aug 20 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. –  Craig Ringer Aug 20 at 13:45

My original solution:

  SELECT a, b 
  FROM tbl t1
  WHERE t1.a = 1 AND NOT EXISTS (
    SELECT 1 FROM tbl t2
    WHERE t2.a = 2 AND t2.b = t1.b
  UNION
  SELECT a, b 
  FROM tbl t1
  WHERE t1.a = 2 AND NOT EXISTS (
    SELECT 1 FROM tbl t2
    WHERE t2.a = 1 AND t2.b = t1.b
  );
share|improve this answer
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. Aug 20 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. –  Erwin Brandstetter Aug 20 at 15:27
    
@ErwinBrandstetter: it is a possible answer, though some others are better. –  Pavel V. Aug 20 at 17:09
    
Then you did it right, I'd say. –  Erwin Brandstetter Aug 20 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
    UNION ALL
    select * from Table2
)
, Subtracted as
(
    select * from Table1
    INTERSECT ALL
    select * from Table2
)
, Unmatched as
(
    select * from Added
    EXCEPT 
    select * from Subtracted
)
select
    t.*
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.

share|improve this answer
    
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 –  Erwin Brandstetter Aug 20 at 22:16
    
Thanks @Erwin; see the amended answer for what I changed. –  Michael Green Aug 20 at 23:44

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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