Write the correlated subquery in the WHERE clause as join

Question

I have a request as shown 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 a way to avoid the subquery in the where section and use it from the sentence itself without sacrificing performance?

Answer 1

You can use INNER JOIN as:

 select a.id, a.title, a.description from my_table_name as a INNER JOIN another_table as b ON (a.id = b.id and b.id = 1) 

or

 select a.id, a.title, a.description from my_table_name as a INNER JOIN another_table as b ON a.id = b.id where b.id = 1 

Both queries may not return the same value for you. You can choose everything that works for you. Use this as a starting point, not as code for copying.

Answer 2

Both answers given so far are incorrect in the general case (although the database may have unique restrictions that guarantee that they are true in a particular case)

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

A general census 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 dependent.

Answer 3

To express this as a mix:

 select distinct a.id, a.title, a.description from my_table_name as a join another_table b on b.id = a.id where b.id = 1 

Using distinct means getting the same results if another_table has the same identifier more than once, so the same row is not returned multiple times.

Note: if the combinations id, name and description in my_table_name are not unique, this query will not return duplicates such as the original query.

---

To ensure you get the same results, you need to make sure that the identifier in the other_table is unique. To do this as a join:

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