CODEWITHSUNDEEP
sql
Nagarjun
Nagarjun

Reputation: 2476

Write correlated subquery in a WHERE Clause as join

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?

Upvotes: 6

Views: 6365

Answers (3)

Vivek Jain
Vivek Jain

Reputation: 3889

You may 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 the queries may not return the same value for you. You may choose whatever works for you. Please use this as a starting point and not as a copy-paste code.

Upvotes: 2

Bohemian
Bohemian

Reputation: 425198

To express it as a join:

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

The use of distinct is to produce the same results in case another_table has the same id more than once so the same row doesn't get returned multiple times.

Note: if combinations of id, name and description in my_table_name are not unique, this query won't return such duplicates as the original query would.

To guarantee to produce the same results, you need to ensure that the id's in another_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

Upvotes: 2

Martin Smith
Martin Smith

Reputation: 453608

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.

Upvotes: 3

Related Questions