sql join which tells me if ID exists in other table

Question

I have 2 tables:

 A      B
 --    ----
 ID    FKID
 --    ----
 1      3
 2      3   
 3      4
 4      4

I need a select statement which shows me all of A with a field that tells me if table B has any ids that match that ID.

Desired Result
-----------
 ID | hasB
-----------
 1    no
 2    no    
 3    yes
 4    yes

Answer 1

In SQL Server this would be the most efficient way rather than doing an OUTER JOIN then removing the duplicates with DISTINCT. Not sure for postgres, you'd need to check the plans.

SELECT ID,
       CASE
         WHEN EXISTS (SELECT *
                      FROM   B
                      WHERE  B.FKID = A.ID) THEN 'yes'
         ELSE 'no'
       END AS hasB
FROM   A  

Answer 2

SELECT DISTINCT
    a.ID,
    CASE WHEN b.FKID IS NULL THEN 'no' ELSE 'yes' END AS hasB
FROM
    tableA a LEFT JOIN
    tableB b ON a.ID = b.FKID