SQL - Replacing slow "IN" statement with a JOIN

Question

As per subject - I am trying to replace slow SQL IN statement with an INNER or LEFT JOIN. What I am trying to get rid of:

SELECT 
    sum(VR.Weight) 
FROM 
    verticalresponses VR
WHERE RespondentID IN
(
    SELECT RespondentID FROM verticalstackedresponses VSR WHERE VSR.Question = 'Brand     Aware'
)

The above I tried replacing with

SELECT 
    sum(VR.Weight) 
FROM 
    verticalresponses VR
    LEFT/INNER JOIN verticalstackedresponses VSR ON VSR.RespondentID = VR.RespondentID     AND VSR.Question = 'Brand Aware'

but unfortunately I'm getting different results. Can anyone see why and if possible advise a solution that will do the job just quicker? Thanks a lot!

Is the `WHERE` for the inner `SELECT` happening on an indexed column? — Dominic Zukiewicz, Jun 10 '13 at 11:33
@DominicZukiewicz - the WHERE is not happening on indexed column and FYI - I have already tried replacing the WHERE with an AND VSR.Question = 'Brand Aware' on the actual join itself... Still no luck — Seb, Jun 10 '13 at 11:38
@SebastianK - if WHERE isn't indexed, it needs to go through every row - a Table Scan - which could be killing your performance. Try adding Full Text Indexing to this column. — Dominic Zukiewicz, Jun 10 '13 at 11:43

score 2 · Accepted Answer · answered Jun 10 '13 at 11:40

The subquery

SELECT RespondentID FROM verticalstackedresponses VSR WHERE VSR.Question = 'Brand     Aware'

could maybe be returning multiple rows for any RespondentID, then you would get different results between join and in versions

Something along the lines of this may give the same results

SELECT 
    sum(VR.Weight) 
FROM 
    verticalresponses VR
JOIN(     SELECT distinct RespondentID FROM verticalstackedresponses 
          WHERE VSR.Question = 'Brand     Aware' 
    ) VSR
    ON  VSR.RespondentID = VR.RespondentID

score 1 · Answer 2 · edited May 23 '17 at 12:11

1

A JOIN will multiply rows because it's an "Equi join"
IN (and EXISTS) will not multiply rows because these are "Semi joins"

Either way, you need suitable indexes, probably

verticalresponses, (RespondentID)
verticalstackedresponses, (Question, RespondentID)

See Using 'IN' with a sub-query in SQL Statements for more

edited May 23 '17 at 12:11

Community

1
1

answered Jun 10 '13 at 11:40

gbn

422,506
82
585
676

score 1 · Answer 3 · answered Jun 10 '13 at 11:41

The problem may be related to duplicates. Try this:

SELECT sum(VR.Weight) 
FROM verticalresponses VR inner join
     (select distinct RespondentID
      from verticalstackedresponses VSR
      where SR.Question = 'Brand Aware'
     ) vsr
     ON VSR.RespondentID = VR.RespondentID;

You want an inner join because you want to filter the VR table. A left join will keep everything in the first table.

SQL - Replacing slow "IN" statement with a JOIN

3 Answers3