Matching Missing Field

Question

Post reply

Matching Missing Field

Mark Grout

SSChasing Mays

Points: 602
More actions
January 16, 2013 at 8:05 pm

#272897

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

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

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

Lokesh Vij SSChampion Points: 10836 More actions · Answer 1

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

angeloc SSC Enthusiast Points: 138 More actions · Answer 2

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

demonfox SSCertifiable Points: 6289 More actions · Answer 3

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:

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

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

Ajay Reddy Right there with Babe Points: 750 More actions · Answer 5

Ajay Reddy

Right there with Babe

Points: 750

January 16, 2013 at 11:46 pm

#1578097

Good Question

thanks

Thanks&Regards
AJAY REDDY.L

Stuart Davies SSCoach Points: 19505 More actions · Answer 6

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

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 7

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/

Dineshbabu Hall of Fame Points: 3220 More actions · Answer 8

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..

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 9

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/

sqlnaive SSCoach Points: 17437 More actions · Answer 10

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. 🙂

Dineshbabu Hall of Fame Points: 3220 More actions · Answer 11

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..

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 12

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/

Dineshbabu Hall of Fame Points: 3220 More actions · Answer 13

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..

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 14

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/