Note this part of query 1: [join on and]

    --query1
    select 
    a.Id,b.CustomerId
    from customGroup b join strategy a on a.GroupId=b.GroupId 
    join customGroup c on b.CustomerId=c.CustomerId and c.GroupId=19
    --query2
    select 
    a.Id,b.CustomerId
    from customGroup b join strategy a on a.GroupId=b.GroupId 
    where b.GroupId=19

I executed them,and got different result,here it is. The result image

CodePudding user response：

You could probably have found that out by running them - but in terms of what they return they should be equivalent.

However the first query would probably by marginally slower than the other query, by the logic of it having to look at a third table (even if it is one of the tables it's already looking at).

Personally I'd rejig the second query myself.

SELECT s.Id,
       g.CustomerId
FROM strategy s 
INNER JOIN customGroup g -- SQL uses inner joins unless specified otherwise, so saying INNER doesn't make a difference
           ON g.GroupID = s.GroupID
              AND g.GroupID = 19;

Does the same thing and seems more readable. As for the speed differential, you'd have to run it with the live query statistics to check - but it might be negligibly quicker

CodePudding user response：

Both query are not similar query.

Note: You have an error on your transcription the value of filter is '10' not '19'.

Query 1: works join the tables strategy and customgroup by groupid, so in this grup do you have all records from the result (ids:67,68,71,73,74,76,77), in the second join you create a subgroup of b (but is a new different "table") only with values groupid=10, so this section dont add any new record to the result, and your coundition no affect the previos join , beacuase work only for table "c".

Query 2: in this case the filter work on all results, and this is wy the result are not the same.

You can replicate with this values.