I'm trying to COUNT records on one table where both the counted records and their "previous" records exist either in the same table or on another.

The table that I'm sampling has a composite PRIMARY KEY consisting of a bigint and character. The other table has the same structure.

A "previous" record has the same character and a bigint - 1 as the counted record either on the same table or on the other table.

In pseudo:



counting_table will never have a parallel record on other_table because the parallel record on counting_table is deleted when its primary key equivalent is inserted into other_table.

I thought I could do this with subqueries, but I can't seem to figure out how to access the outer query's data from inside the subquery.

How can my intent be implemented optimally?


One way would be to LEFT JOIN both tables (counting_table itself as well as other_table), and count a COALESCE expression, which will be NULL (and therefore not counted) if neither table has a "previous" record:

SELECT count(COALESCE(o.bigint_col, c1.bigint_col)) AS ct
FROM   counting_table c
LEFT   JOIN other_table o ON o.bigint_col = c.bigint_col - 1
                         AND o.char_col   = c.char_col
LEFT   JOIN counting_table c1 ON c1.bigint_col = c.bigint_col - 1
                             AND c1.char_col   = c.char_col;

The other option would be with two OR'ed EXISTS semi-joins:

SELECT count(*) AS ct
FROM   counting_table c
   SELECT 1 FROM other_table o
   WHERE  o.bigint_col = c.bigint_col - 1
   AND    o.char_col   = c.char_col
   SELECT 1 FROM counting_table c1
   WHERE  c1.bigint_col = c.bigint_col - 1
   AND    c1.char_col   = c.char_col

I prefer the second variant as it also works with multiple "previous" rows matching in one of both related tables - which does not seem to apply to your case, but it's still the safer bet. Also typically a bit faster.

  • @Gracchus: It depends on your environment. Test both with EXPLAIN ANALYZE. Run a couple of times and take the best time to exclude caching artifacts. My money is on EXISTS ... Drop a line here as to the outcome if you don't mind. – Erwin Brandstetter May 8 '14 at 13:46
  • 1
    @Gracchus: Nitpicking: EXISTS semi-joins are not exactly subqueries (that shows in the query plan). Results would be welcome! – Erwin Brandstetter May 8 '14 at 15:06

Inner join on counting_table.bigint-1 = other_table.bigint and counting_table.char = other_table.char

FROM counting_table t1 
INNER JOIN other_table t2 
    ON t1.bigint_column-1 = t2.bigint_column 
    AND t1.character_column=t2.character_column

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy