Post reply

Join

  • May 21, 2012 at 5:44 am

    #261474

    What is diff betn join and left join .Plz explain with example

  • May 21, 2012 at 5:49 am

    #1490362

    This is from BOL.It has nice explaination of joins.

    http://msdn.microsoft.com/en-us/library/ms191472(v=sql.100).aspx

    GulliMeel

    Finding top n Worst Performing queries[/url]
    Improve the performance of Merge Join(special case)
    How to Post Performance Problem -Gail Shaw[/url]

  • May 21, 2012 at 6:17 am

    #1490375

    Agreed, BOL will answer this for you.

    However...

    CREATE TABLE firstTable (uid INT, data VARCHAR(3) )

    CREATE TABLE secondTable (uid INT, data VARCHAR(3) )

    INSERT INTO firstTable VALUES (1, 'abc')

    INSERT INTO firstTable VALUES (2, 'bcd')

    INSERT INTO firstTable VALUES (3, 'cde')

    INSERT INTO secondTable VALUES (1, 'abc')

    INSERT INTO secondTable VALUES (3, 'bcd')

    INSERT INTO secondTable VALUES (4, 'cde')

    SELECT ft.uid, ft.data FROM firstTable ft INNER JOIN secondTable st ON ft.uid = st.uid

    Will return:

    1, 'abc'

    3, 'cde'

    SELECT ft.uid, ft.data FROM firstTable ft LEFT JOIN secondTable st ON ft.uid = st.uid

    Will return:

    1, 'abc'

    2, 'bcd'

    3, 'cde'

    Because it returns what the INNER JOIN does plus any NULL returns on the right side of the join.

    SELECT ft.uid, ft.data FROM firstTable ft RIGHT JOIN secondTable st ON ft.uid = st.uid

    1, 'abc'

    3, 'cde'

    NULL, NULL

    Because it returns what the INNER JOIN does plus any NULL returns on the left side of the join.

    SELECT ft.uid, ft.data FROM firstTable ft CROSS JOIN secondTable st

    1, 'abc'

    2, 'bcd'

    3, 'cde'

    1, 'abc'

    2, 'bcd'

    3, 'cde'

    1, 'abc'

    2, 'bcd'

    3, 'cde'

    Will return all possible results (3^3 in this case) of all possible JOINs.

    For more information review the previous BOL link.

    ---

    Note to developers:
    CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
    So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

    Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
    My blog: http://uksqldba.blogspot.com
    Visit http://www.DerekColley.co.uk to find out more about me.

  • May 21, 2012 at 1:48 pm

    #1490687

    have a look at this link, it shows you the joins in picture form too

    http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins

    ***The first step is always the hardest *******

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

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