Efficient way to check if row exists for multiple records in postgres

Question

I saw answers to a related question, but couldn't really apply what they are doing to my specific case.

I have a large table (300k rows) that I need to join with another even larger (1-2M rows) table efficiently. For my purposes, I only need to know whether a matching row exists in the second table. I came up with a nested query like so:

SELECT 
  id, 
  CASE cnt WHEN 0 then 'NO_MATCH' else 'YES_MATCH' end as match_exists
FROM 
  (
   SELECT 
     A.id as id, count(*) as cnt
   FROM
     A, B
   WHERE 
     A.id = B.foreing_id
   GROUP BY A.id
  ) AS id_and_matches_count

Is there a better and/or more efficient way to do it?

Thanks!

user1919238 · Accepted Answer · 2014-03-27 08:22:00Z

3

You just want a left outer join:

SELECT 
   A.id as id, count(B.foreing_id) as cnt
FROM A
LEFT OUTER JOIN B ON
    A.id = B.foreing_id
GROUP BY A.id

answered Mar 27, 2014 at 8:22

user1919238

Sign up to request clarification or add additional context in comments.

3 Comments

Tomato Over a year ago

actually, my attempt was incorrect. thanks for providing proper query, but is there more efficient way to do it considering the fact that I only need to know whether count(B.foreign_id) is = 0 or > 0?

user1919238 Over a year ago

@Tomato, if there were a way to select only one row from B for each A, you could avoid the GROUP BY. Like, if the first row for B that exists were always labeled 1 in another column, you could add a condition for that and avoid grouping. Otherwise, this is pretty much it.

Tomato Over a year ago

thanks a lot for pointers! There is no additional column in our current schema, thus I will stick with current approach for now.

Collectives™ on Stack Overflow

Efficient way to check if row exists for multiple records in postgres

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related