November 17, 2014 at 12:29 pm
I have come across a few tables that are created using dot notation, but not all of the columns are referenced.
Notice the dummy script below. The first col is listed as A dot, but the second and third do not have the notation. The 4th column, Order_No, is labeled but then nothing after that. Should I assume that after A.Date, Customer_NM and Customer_Acct_No as both from table A? While Price and Currency are both from table B ?
SELECT
A.Date
,Customer_NM
,Customer_ACCT_NO
,B.Order_No
,Price
,Currency
FROM Table1 A
Left Join Table2 B
on A.x = B.x
November 17, 2014 at 12:36 pm
mcinvalek (11/17/2014)
I have come across a few tables that are created using dot notation, but not all of the columns are referenced.Notice the dummy script below. The first col is listed as A dot, but the second and third do not have the notation. The 4th column, Order_No, is labeled but then nothing after that. Should I assume that after A.Date, Customer_NM and Customer_Acct_No as both from table A? While Price and Currency are both from table B ?
SELECT
A.Date
,Customer_NM
,Customer_ACCT_NO
,B.Order_No
,Price
,Currency
FROM Table1 A
Left Join Table2 B
on A.x = B.x
No you cannot assume that just by the script.
You can know that A.Date and B.Order_No were aliased. Likely that is because both may exist in both tables.
But what you can know for sure is that the columns that are not prefixed with an alias are unique in the query set so the alias is not necessary though it would have been better to include the alias on all columns.
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
November 17, 2014 at 12:45 pm
Much appreciated. This is a corporate environment and you know how sloppy some coders can be.
Thanks for the info
November 17, 2014 at 12:54 pm
mcinvalek (11/17/2014)
Much appreciated. This is a corporate environment and you know how sloppy some coders can be.Thanks for the info
Not limited to corporate environments. I call it lazy coding regardless of environment. Even in single table queries I recommend using table aliases in the FROM clause and using that alias on the columns in the select list. I have seen many single table queries grow into multi-table queries and if you are in the habit of using aliases things seem to work better as changes are made.
November 18, 2014 at 1:36 am
Lynn Pettis (11/17/2014)
Even in single table queries I recommend using table aliases in the FROM clause and using that alias on the columns in the select list.
I would go a bit further and recommend using sensible aliases. Aliasing tables A, B, C does nothing for clarity, exactly the opposite. The alias should be understandable, or at least I shouldn't have to check the FROM repeatedly to remind myself whether Customers or Orders is aliased B.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 18, 2014 at 10:23 am
GilaMonster (11/18/2014)
Lynn Pettis (11/17/2014)
Even in single table queries I recommend using table aliases in the FROM clause and using that alias on the columns in the select list.I would go a bit further and recommend using sensible aliases. Aliasing tables A, B, C does nothing for clarity, exactly the opposite. The alias should be understandable, or at least I shouldn't have to check the FROM repeatedly to remind myself whether Customers or Orders is aliased B.
I actually agree with this statement. But I would also say it should be an abbreviation or acronym not the name of the table. I have used three to five letter aliases to identify tables Seems to work, at least where I am at the moment.
November 18, 2014 at 10:35 am
Lynn Pettis (11/18/2014)
GilaMonster (11/18/2014)
Lynn Pettis (11/17/2014)
Even in single table queries I recommend using table aliases in the FROM clause and using that alias on the columns in the select list.I would go a bit further and recommend using sensible aliases. Aliasing tables A, B, C does nothing for clarity, exactly the opposite. The alias should be understandable, or at least I shouldn't have to check the FROM repeatedly to remind myself whether Customers or Orders is aliased B.
I actually agree with this statement. But I would also say it should be an abbreviation or acronym not the name of the table. I have used three to five letter aliases to identify tables Seems to work, at least where I am at the moment.
And since we are on the topic, I recommend using the same alias for a table every time it is aliased. Reduce the guessing games even further.
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
November 18, 2014 at 11:46 am
SQLRNNR (11/18/2014)
Lynn Pettis (11/18/2014)
GilaMonster (11/18/2014)
Lynn Pettis (11/17/2014)
Even in single table queries I recommend using table aliases in the FROM clause and using that alias on the columns in the select list.I would go a bit further and recommend using sensible aliases. Aliasing tables A, B, C does nothing for clarity, exactly the opposite. The alias should be understandable, or at least I shouldn't have to check the FROM repeatedly to remind myself whether Customers or Orders is aliased B.
I actually agree with this statement. But I would also say it should be an abbreviation or acronym not the name of the table. I have used three to five letter aliases to identify tables Seems to work, at least where I am at the moment.
And since we are on the topic, I recommend using the same alias for a table every time it is aliased. Reduce the guessing games even further.
And I will also agree with this. Now, I have had to modify this on occasion when I had to join the same table 2 or 3 times in a query to get the answers we needed.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply