SQL: How to check if row in relation exist?

Question

I have a first table with and a related second table with a foreign key to the first table.

The number of rows in the second table can range from zero rows up to many millions or rows (timeseries data).

When displaying rows of the first table to the end user I want to show whether there are related rows in the second table - either yes or no, not more.

Currently I left join the tables and make a count on the second table. But I suspect this is a slow and resource consuming way of just determining if there are related rows in the second table.

SELECT
    first.id,
    first.title,
    Count( second.id ) as count
FROM
    first
LEFT JOIN
    second
ON
    first.id = second.first_id
GROUP BY
    first.id
ORDER BY
    count DESC

On both tables there are indexes in the primary keys id. The database ist PostgrSQL.

What is a faster and / or less resource consuming way to go here?

Answer 1

I would do that with a scalar sub-select, not a join:

select f.*, 
       exists (select * from second s where s.first_id = f.id) as rows_in_second_exists
from first

The exists operator will stop looking into the second table as soon as a row is found. An index on second (first_id) will speed this up.