translating sql sub query to join

Question

I had a long query, I short it out by using joins instead and resultant query is as below but still it has sub query. How to convert this sub query to join

SELECT 
    pav.post_id as Id, img.path as Path, attr.name as Name, pc.title as Category, pav.value_text as Valuess, post.created_on as createdOn 
FROM 
    postings post inner join post_attributes_values pav on post.post_id = pav.post_id
    left outer join images img on post.post_id = img.post_id and img.sequence='1' 
    inner join attributes attr on pav.attr_id = attr.attr_id 
    inner join categories_parent_categories pc on attr.cat_id = pc.category_id 
where 
    pav.post_id in  (select distinct post_id from post_attributes_values where value_text = 'SFX')

Is distinct necessary? Do you search for specific attribute with value 'SFX' or any attribute will do? — Nikola Markovinović, Aug 03 '12 at 10:52
@NikolaMarkovinović: The design of table is like that distinct is necessary. — Sandeep Kumar, Aug 03 '12 at 11:02
Distinct shouldn't be nessessary in the sub query because you are just checking for in.... — Bob Vale, Aug 03 '12 at 11:37
@BobVale: replacing sub query with join to improve performance — Sandeep Kumar, Aug 03 '12 at 12:22
@Sandy if this is MS Sql using `where exists (select post_id from post_attribute_values where value_text='SFX' and post_id=pav.post_id)` should be one of the faster performers — Bob Vale, Aug 03 '12 at 13:09

score 1 · Accepted Answer · answered Aug 03 '12 at 11:31

After reading your last comment to Matei's answer I have come to realize that you actually want ALL the posts where one of the attributes has value of 'SFX'. If I understood correctly, your only alternative is to add derived table and join by post_id:

SELECT pav.post_id     AS Id,
       img.path        AS Path,
       attr.name       AS Name,
       pc.title        AS Category,
       pav.value_text  AS Valuess,
       post.created_on AS createdOn
FROM   postings post
       INNER JOIN post_attributes_values pav
               ON post.post_id = pav.post_id
       LEFT OUTER JOIN images img
                    ON post.post_id = img.post_id
                       AND img.sequence = '1'
       INNER JOIN attributes attr
               ON pav.attr_id = attr.attr_id
       INNER JOIN categories_parent_categories pc
               ON attr.cat_id = pc.category_id
       INNER JOIN
       (
             SELECT DISTINCT post_id
             FROM   post_attributes_values
             WHERE  value_text = 'SFX'
       ) sfxPosts
               ON pav.post_id = sfxPosts.post_id

(Query reformatted thanks to instant sql formatter.)

will there be any performance improvement by replacing sub-query with derived table? — Sandeep Kumar, Aug 03 '12 at 12:23
@Sandy [This man explained it](http://stackoverflow.com/questions/1200295/sql-join-vs-in-performance). It also depends heavily on your sql engine; there is no silver bullet. Best is to test it yourself. — Nikola Markovinović, Aug 03 '12 at 12:28

Mihai Matei · Answer 2 · 2012-08-03T10:58:32.630

0

Maybe this? Please test it

SELECT 
    pav.post_id as Id, img.path as Path, attr.name as Name, pc.title as Category, pav.value_text as Valuess, post.created_on as createdOn 
FROM 
    postings post 
  inner join post_attributes_values pav on post.post_id = pav.post_id AND pav.value_text = 'SFX'
  left outer join images img on post.post_id = img.post_id and img.sequence='1' 
  inner join attributes attr on pav.attr_id = attr.attr_id 
  inner join categories_parent_categories pc on attr.cat_id = pc.category_id

edited Aug 03 '12 at 10:58

answered Aug 03 '12 at 10:53

Mihai Matei

24,166
5
32
50

`pav` is mentioned twice. You might want to remove last occurrence. – Nikola Markovinović Aug 03 '12 at 10:57
I already have tried this but returned rows are not as expected. – Sandeep Kumar Aug 03 '12 at 11:04
1

What's wrong? give me please an example of rows which are returned and what should be returned instead – Mihai Matei Aug 03 '12 at 11:10

translating sql sub query to join

2 Answers2