Querying a table finding if child table's matching records exist in ANSI SQL

Question

I have two tables A and B where there is one-to-many relationship.

Now I want some records from A and with this existence field that shows if B has any matching records. I don't want to use the count function as B has too many records that delays SQL execution. Either I don't want to use proprietary keywords like rownum of Oracle like below, as I need as much compatibility as possible.

select A.*, (
    select 1 from B where ref_column = A.ref_column and rownum = 1
) existence
...

Answer 1

Use an EXISTS clause. If the foreign key in B is indexed, performance should not be an issue.

SELECT *
FROM a
WHERE EXISTS (SELECT 1 FROM b WHERE b.a_id = a.id)

Answer 2

You would use left join + count anyway, select statement in select list can be executed multiple times while join will be done only once.

Also you can consider EXISTS:

select A.*, case when exists (
    select 1 from B where ref_column = A.ref_column and rownum = 1
) then 1 else 0 end