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

Question

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
      table1.userID,
      table2.UniqueID,
      table3.entrydate,
      table2.entrytime,
      table4.changedatestart,
      table2.changetimestart,
      table5.changedateend,
      table2.changetimeend,
      table6.leavedate,
      table2.leavetime,
      table7.nationality_ID,
      table8.reg_code
      FROM
      table1 INNER JOIN table2 ON (table1.t1_KEY=table2.t2_KEY)
       RIGHT OUTER JOIN DATETIMETABLE  table4 ON (change_start_date_alias.DATEKEY=table2.change_date_start_KEY)
       RIGHT OUTER JOIN DATETIMETABLE  table5 ON (change_end_date_alias.DATEKEY=table2.change_date_end_KEY)
       RIGHT OUTER JOIN DATETIMETABLE  table3 ON (entry_date_alias.DATEKEY=table2.ENTRY_DATE_KEY)
       RIGHT OUTER JOIN DATETIMETABLE  table6 ON (leave_date_alias.DATEKEY=table2.LEAVE_DATE_KEY)
       RIGHT OUTER JOIN table7 ON (table7.nat_KEY=table2.nat_KEY)
       INNER JOIN table8 ON (table8.reg_DEPT_KEY=table2.reg_DEPT_KEY)
       RIGHT OUTER JOIN dbo.table9 ON (table9.leave_KEY=table2.leave_KEY)
    INNER JOIN
        (SELECT 
        table1.userID UID,
        ROW_NUMBER() OVER (PARTITION BY table1.userID ORDER BY table3.entrydate ASC) as Seq,
        table2.UniqueID "Unique_ID",
        table3.entrydate "entry_date",
        table2.entrytime "entry_time",
        table4.changedatestart "change_start_Date",
        table2.changetimestart "change Start Time",
        table5.changedateend "change End Date",
        table2.changetimeend "change End Time",
        table6.leavedate "leave Date",
        table2.leavetime "leave time",
        table7.nationality_ID "Nationality ID",
        table8.reg_code "Registration Code"
        FROM
        table1 INNER JOIN table2 ON (table1.t1_KEY=table2.t2_KEY)
        RIGHT OUTER JOIN DATETIMETABLE  table4 ON (change_start_date_alias.DATEKEY=table2.change_date_start_KEY)
        RIGHT OUTER JOIN DATETIMETABLE  table5 ON (change_end_date_alias.DATEKEY=table2.change_date_end_KEY)
        RIGHT OUTER JOIN DATETIMETABLE  table3 ON (entry_date_alias.DATEKEY=table2.ENTRY_DATE_KEY)
        RIGHT OUTER JOIN DATETIMETABLE  table6 ON (leave_date_alias.DATEKEY=table2.LEAVE_DATE_KEY)
        RIGHT OUTER JOIN table7 ON (table7.nat_KEY=table2.nat_KEY)
        INNER JOIN table8 ON (table8.reg_DEPT_KEY=table2.reg_DEPT_KEY)
        RIGHT OUTER JOIN dbo.table9 ON (table9.leave_KEY=table2.leave_KEY)
        WHERE table3.entrydate  BETWEEN  '20131201'  AND  '20140531'
        AND table8.reg_DESC  In  ( 'Value1','Value2','Value3','Value4','Value5'  )
        AND table9.leave_CODE IN ('11','15','16','22','25','27','54','57','66')
        )b
    ON b.UID = table1.userID
    **AND b.Unique_ID <> table2.UniqueID**
    AND b.Seq = 1
    AND b.[Registration Code] = table8.reg_code << If this line is commented out the same row count is returned whether WHERE/AND is used in not equal to ID statement:

AND/WHERE b.Unique_ID <> table2.UniqueID

But if I reintroduce the last lin and change the WHERE/AND used in the not equal to ID statement it gives slightly more rows when using the AND statement as opposed to the WHERE predicate.

You're referencing table C, which is a part of your derived table, in the outer query, and further, you're joining it to the derived table E. I'm a little surprised this doesn't raise an error, but I'm not surprised it gives unexpected results. And how does E even have a column f, if you don't include it in the derived table's select list? — Tab Alleman, Jan 22 '15 at 13:32
Can you paste the actual code please? This one is very misleading. — SouravA, Jan 22 '15 at 14:05
@Sourav_Agasti I can't post specific table names and columns verbatim as it's confidential but I will try and change the names in the actual query and post it. — Andrew, Jan 22 '15 at 14:09
Original post I have added query but using aliases and simplified explanation of problem. — Andrew, Jan 22 '15 at 15:10

score 0 · Answer 1 · edited May 23 '17 at 11:50

0

In the original edit, you had inner join in example, thus I asked you to post the original code.

Please keep in mind, RIGHT OUTER JOIN is very very different from INNER JOIN in handling the ON clause. In a right outer join, you get all the records from the right table irrespective of the AND on the right table.

So, in the below query,

SELECT * FROM Table1 RIGHT OUTER JOIN Table2 ON Table1.ID = Table2.Id
AND TABLE2.ID = 2

you get all the records from Table2 even when you have put TABLE2.ID = 2. It just has no effect. Whereas, in the below query,

SELECT * FROM Table1 RIGHT OUTER JOIN Table2 ON Table1.ID = Table2.Id
WHERE TABLE2.ID = 2

You will get records only for TABLE2.ID = 2.

But for an inner join, both the below queries are similar:

SELECT * FROM Table1 INNER JOIN Table2 ON Table1.ID = Table2.Id
AND TABLE2.ID = 2
SELECT * FROM Table1 INNER JOIN Table2 ON Table1.ID = Table2.Id
WHERE TABLE2.ID = 2

So please handle AND conditions with care when you are not talking about RIGHT JOINS!

I had a similar question sometime back which I had asked here.

edited May 23 '17 at 11:50

Community

1
1

answered Jan 22 '15 at 16:13

SouravA

5,147
2
24
49

Can you explain what join of the query you are discussing please? I have had 'inner join' for the join between outer query and sub-query the whole time if this is what you are pointing at. Thanks. – Andrew Jan 22 '15 at 16:31
You are having something like table2 RIGHT OUTER JOIN....... b. Thus, the condition `AND b.Unique_ID <> table2.UniqueID` is quite different from `WHERE b.Unique_ID <> table2.UniqueID`. – SouravA Jan 22 '15 at 16:37
I understand what you are saying from the link you gave and the description above but I am looking at the joins for the last condition (table 8), which is affecting the returned results when I use and there is no outer join on that table 8. Table 8 uses inner join so from that I would assume that changing AND/WHERE for unique ID shouldn't make any difference to the returned results, which it does unless I remove last statement for join to table 8. – Andrew Jan 23 '15 at 08:37

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

1 Answers1