Post reply

Joint two tables on a non-key field

  • July 4, 2015 at 10:20 pm

    #398691

    I have two tables tbl1 and tbl2, which I do a full outer join between tbl1 and tbl2 on recordId field. The recordId field is not a key in either of the tables.

    If there is one row each for a recordId 123 in both tables, the select query would return one combined row.

    If tble1 had two rows for recordId 123, and tbl2 had one row for the same, it would return to rows repeating the data in tbl2.

    If tbl2 had two rows and bl1 had one row, it would return two rows in output repeating the data in tbl1.

    Is the above correct?

    Would the result be different if it was an inner join instead of full outer join?

    Is it ever possible that one of the two records with recordId 123 will be dropped from the result?

    Thanks in advance for your help.

  • July 4, 2015 at 11:44 pm

    #1810306

    tinausa (7/4/2015)

    I have two tables tbl1 and tbl2, which I do a full outer join between tbl1 and tbl2 on recordId field. The recordId field is not a key in either of the tables.

    If there is one row each for a recordId 123 in both tables, the select query would return one combined row.

    If tble1 had two rows for recordId 123, and tbl2 had one row for the same, it would return to rows repeating the data in tbl2.

    If tbl2 had two rows and bl1 had one row, it would return two rows in output repeating the data in tbl1.

    Is the above correct?

    Yes

    Would the result be different if it was an inner join instead of full outer join?

    Given this scenario - no. Where it would differ is if one table had the records and the other table did not. Inner join would not return the rows since the rows did not exist in both tables. The outer join would continue to return the rows.

    Is it ever possible that one of the two records with recordId 123 will be dropped from the result?

    Yes. One such scenario would be if the NoLock directive were to be used in the query.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • July 5, 2015 at 6:23 am

    #1810313

    Thanks SQLRNNR for your help.

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

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