jeudi 22 janvier 2015

Difference between 'where' and 'And' in in line view query join

I had read in this thread below that there is no difference in the data returned in a query where you either have a Where predicate after the join or an 'And':


description of difference between 'where' and 'And' in join


However I do have different row counts in my query, which is using an in-line view query in my overall statement, when I change AND/WHERE. I have noticed that the issue is happening when I use a row number over statement in my in line query.


The reason I am using this is to restrict records in the in line query to only the first date record, so I can return subsequent but related records from the outer query.


However when I change the WHERE to an AND I get different number of rows.


My query is similar to this:



select distinct a.1 f,
a.2 g,
b.1 h,
b.2 i
From a inner join b ON (a.key = b.key)
inner join (select c.1,
c.2,
d.1,
d.2,
ROW_NUMBER() OVER (PARTITION BY c.1 ORDER BY d.2 ASC) Seq
from c inner join d ON (c.key = d.key)
) e
on e.f = c.1
**and e.g <> c.2 << (this can also be 'WHERE e.g <> c.2')**
and e.Seq = 1
and e.i = d.2


When I change WHERE/AND I get slightly different figures. Where gives me slightly more but not much. the <> is the unique ID of the row so I am trying to exclude the exact same record from inner query to what is returned from outer query.


I also found out that if I remove the last condition (and e.i = d.2) and then change AND/WHERE the row count returned stays the same.


I realise that technically changing the WHERE/AND without the sequence condition is not changing the rows count but it's more that I am wondering why including the sequence and then changing the WHERE/AND then gives me different results.


What is causing this change in row count returned with changing AND/WHERE with the sequence?


EDIT: The problem really just seems to be the last condition that is having an affect on the rows count i.e. when include this in query and change WHERE/AND then I get different row count.


Aucun commentaire:

Enregistrer un commentaire