Matching Missing Field

  • Comments posted to this topic are about the item Matching Missing Field

  • I did observed this strange behavior, but was not aware of the reason behind this. Thanks 🙂

    ~ Lokesh Vij


    Guidelines for quicker answers on T-SQL question[/url]
    Guidelines for answers on Performance questions

    Link to my Blog Post --> www.SQLPathy.com[/url]

    Follow me @Twitter

  • well that's interesting. Certainly wouldn't want to rely on it, unless that is behavior in the standard.

  • Lokesh Vij (1/16/2013)


    I did observed this strange behavior, but was not aware of the reason behind this. Thanks 🙂

    +1

    good question ..thanks...

    ~ demonfox
    ___________________________________________________________________
    Wondering what I would do next , when I am done with this one :ermm:

  • EZ PZ

    Thanks

    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

  • Good Question

    thanks

    Thanks&Regards
    AJAY REDDY.L

  • Good question - thanks

    -------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
    There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
    I would never join a club that would allow me as a member - Groucho Marx

  • Dang! You stole my thunder... I thought up a similar question about a week ago, just didn't get around to submitting it yet...

    Thanks! 😉

    angeloc (1/16/2013)


    well that's interesting. Certainly wouldn't want to rely on it, unless that is behavior in the standard.

    Yes, this is standard. And yet, I still would not rely on it. Imagine how your query would suddenly fail if someone adds a column "col1" to table "TblA2"!

    For any query that uses more than a single table, you should always use the table prefix on ALL column references. (And because most table names tend to be long and I prefer my query to be human readable, this automatically implies that you should also always provide an alias for each of the tables used in the query.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Great question and great concept.

    I added COL1 to TblA2 and expected "Ambiguous column name 'col1'.". But this time Sql server selected column from TblA2 and gave me the correct result.

    It will be failed in Joins if we didn't prefix the table with alias name.

    But as Hugo said, we can't predict the output if someone add col1 after sometime. So always it is better to prefix the columns with table alias name.

    --
    Dineshbabu
    Desire to learn new things..

  • Dineshbabu (1/17/2013)


    Great question and great concept.

    I added COL1 to TblA2 and expected "Ambiguous column name 'col1'.". But this time Sql server selected column from TblA2 and gave me the correct result.

    It will be failed in Joins if we didn't prefix the table with alias name.

    That's correct. SQL Server will first try to match an unqualified column reference in the current scope. If that fails, it will go one scope higher, and repeat that as necessary. You will only get the "ambiguous column name" error if there are multiple columns with the same name at the same level.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Good question. Took some time as I get confused in between generating query plan within my mind from these slight confusing queries but finally did it. 🙂

  • Hugo Kornelis (1/17/2013)


    Dineshbabu (1/17/2013)


    Great question and great concept.

    I added COL1 to TblA2 and expected "Ambiguous column name 'col1'.". But this time Sql server selected column from TblA2 and gave me the correct result.

    It will be failed in Joins if we didn't prefix the table with alias name.

    That's correct. SQL Server will first try to match an unqualified column reference in the current scope. If that fails, it will go one scope higher, and repeat that as necessary. You will only get the "ambiguous column name" error if there are multiple columns with the same name at the same level.

    Thanks Hugo.

    Is there any other way to have same column names at the same level without using JOINS.

    --
    Dineshbabu
    Desire to learn new things..

  • Dineshbabu (1/17/2013)


    Hugo Kornelis (1/17/2013)


    Dineshbabu (1/17/2013)


    Great question and great concept.

    I added COL1 to TblA2 and expected "Ambiguous column name 'col1'.". But this time Sql server selected column from TblA2 and gave me the correct result.

    It will be failed in Joins if we didn't prefix the table with alias name.

    That's correct. SQL Server will first try to match an unqualified column reference in the current scope. If that fails, it will go one scope higher, and repeat that as necessary. You will only get the "ambiguous column name" error if there are multiple columns with the same name at the same level.

    Thanks Hugo.

    Is there any other way to have same column names at the same level without using JOINS.

    Hmmm, the only thing I can think of right now would be APPLY (which you could argue to be a special kind of join).


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Hugo Kornelis (1/17/2013)Hmmm, the only thing I can think of right now would be APPLY (which you could argue to be a special kind of join).

    🙂

    What is the Diff B/W Cross Join and Cross Apply? I think i'm going out of scope, else provide me reference to understand it in detail.

    --
    Dineshbabu
    Desire to learn new things..

  • Dineshbabu (1/17/2013)


    What is the Diff B/W Cross Join and Cross Apply? I think i'm going out of scope, else provide me reference to understand it in detail.

    For a JOIN (any join, either INNER, OUTER, or CROSS), both sources must be either a table or view, or a table-valued function or subquery that is completely independant of the rest of the query. So you can not for instance write:

    FROM Table1 AS a CROSS JOIN dbo.TableValuedFunction(a.Column1) AS b

    or

    FROM Table1 AS a CROSS JOIN (SELECT Col1, Col2 FROM Table2 AS b WHERE b.Col3 = a.Col3) AS b

    This is a limitation that comes from the defintion of joins in the ANSI standard, which says to first project both sources, then make each possible row-pair (a cross join), and then filter out those that don't satisfy the join condition. You can not "first" project the second source in this case, because it can be different for each row from the first source.

    Some vendors have chosen to work around this implementation by allowing code like the above. Microsoft has made almost the same decision, but they chose to introduce a special (non-ANSI) keyword for this: APPLY. So the two code fragments above can be made into valid T-SQL code as follows:

    FROM Table1 AS a CROSS APPLY dbo.TableValuedFunction(a.Column1) AS b

    FROM Table1 AS a CROSS APPLY (SELECT Col1, Col2 FROM Table2 AS b WHERE b.Col3 = a.Col3) AS b

    The logical definition of these queries is: for each row in Table1, evaluate the second source (either the function or the subquery) using the "current" row in Table1, then join all the results from the result of that evaluation to that "current" row in Table1. (The physical implementation can be different - in many cases, the optimizer is able to come up with a faster way to achieve the same result).

    And to paint a complete picture - apart from CROSS APPLY, you can also use OUTER APPLY. This works the same, with only one difference - if, for a given row in Table1, the function or subquery produces an empty result set, CROSS APPLY would discard that row from the result (like a CROSS JOIN to an empty table, or an INNER JOIN with no match); OTER APPLY would keep that row in the result set, with NULL values in all the columns that normally come from the function or subquery (similar to an OUTER JOIN).


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Viewing 15 posts - 1 through 15 (of 48 total)

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