Post reply

LEFT JOIN On condition

  • September 29, 2010 at 2:51 pm

    #98908

    SELECT * FROM

    A LEFT OUTER JOIN B ON B.col1 = 1

    LEFT OUER JOIN C ON C.col3=A.col3 AND C.col2=B.col2

    What results does such a left outer join get. When I replace the 'AND' in above query with 'WHERE', it gives me different results.

    Please help me undestand what the left join is doing?

    Thanks and Regards,

    praveena

  • September 29, 2010 at 3:32 pm

    #1229372

    The Left Join can return more rows since it only tries to link table C with B. But if there are no rows in B, you get C's rows anyway. But a WHERE will prevent the rows from showing up unless all of them have a corresponding row in the B table.

    If you use an INNER JOIN, the WHERE and AND will return the same number of rows.

    The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking

  • September 30, 2010 at 1:40 am

    #1229482

    I would say , you yourself should test it with small data set and then you can see the result with different tweaks in the query.and If still you find some issue, post your problem with sample data along with query

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • September 30, 2010 at 7:13 am

    #1229592

    Thank you... I understood it now..

  • September 30, 2010 at 7:15 am

    #1229596

    Thank you Bhuvnesh for your suggestion. I will post the sample data next time..

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply