Write correlated subquery in a WHERE Clause as join

Question

I have a query like below:

select 
  a.id, a.title, a.description
from
  my_table_name as a
where
  a.id in (select id from another_table b where b.id = 1)

My question is, is there any way I can avoid the subquery in where clause and use it in from clause itself without compromising of performance?

Answer 1

Both of the answers given so far are incorrect in the general case (though the database may have unique constraints which ensure they are correct in a specific case)

If another_table might have multiple rows with the same id then the INNER JOIN will bring back duplicates that are not present in the IN version. Trying to remove them with DISTINCT can change the semantics if the columns from my_table_name themselves have duplicates.

A general rewrite would be

SELECT a.id,
       a.title,
       a.description
FROM   my_table_name AS a
       JOIN (SELECT DISTINCT id
             FROM   another_table
             WHERE  id = 1) AS b
         ON b.id = a.id 

The performance characteristics of this rewrite are implementation dependant.