Select records in on table based on conditions from another table?

Question

I have got 2 tables, A, B

A: id is primary key and indexed

id,  type_id,  status
------------------
1,  1,  True
2,  1,  False
3,  2,  False
...

B: (Type) type_id is primary key and indexed

type_id, param
----------
1,  23
2,  35
3,  24

I would like select all rows in B which has at least 1 associated entry in A with status True

select distinct B.id, B.param 
from B
join A on A.type_id = B.type_id
where A.status = true

Is this a good way?

Answer 1

I will do it in this way (it should be valid in most database systems:

select *
from b
where type_id in (
  select type_id
  from a
  where status = true
)

To your question about if yours is a good way, my answer is no, it is not a good way because it likely forces a big intermediate record set (by the joining) then a time consuming distinct on the intermediate record set.

UPDATE

After some thought I realized there is no absolute good or bad solution. It all depends on the data your have in each table (total records, value distribution, etc...). So go ahead with the one that is clearly communicate the intention and be prepared to try different ones when you hit a performance issue in production.

Answer 2

SELECT B.*
FROM B
WHERE B.type_id
IN 
( SELECT A.type_id 
  FROM A WHERE status='True'
);