Enhancing the readability of your code: Table aliasing in sql

  • Feeling hungry, pork chops sound good this am 😉

  • Steve Jones - Editor (5/7/2009)


    For the record, I'm a leading commas guy, Jeff is wrong, and it's easier to read. All you trailing comma folk need to change!:-D:-D:w00t:

    However, I do think that in your group, you need to be consistent. I'd hate to upper case SQL, or go with trailing commas, but I would for consistency's sake.

    My preference is to camel case tables, go with 2-4 letter aliases that are meaningful (as in ALZDBA's example), and stick with lower case for most typing.

    +1. This is almost exactly how I code SQL.

    What's up w/ the leading comma haters? I suppose you put your ands and ors at the end of a line, too? lol...

    Tim

  • tlehner (5/7/2009)


    What's up w/ the leading comma haters? I suppose you put your ands and ors at the end of a line, too? lol...

    Leading commas looks grammatically wrong

    compare (Addresses used to be written with comma's to seperate each address element - at least thats how I was taught)

    Queen of England,

    Buckingham Palace,

    London,

    England

    with

    Queen of England

    ,Buckingham Palace

    ,London

    ,England

  • Gene Marini (5/7/2009)


    Do they have computers in France?

    Probably they do... we have some in Italy as well.

    I find this post very useful for the discussion.

    -- Gianluca Sartori

  • Sylvia is actually right when she says that there's no need for aliases in author-title query. But Jeff is right when he says that code is not written well for other people. Just as case, comma location, and aliasing should be consistent, so shoud the use of table prefixes. Either use them, or don't; don't just use them for ambiguous columns or for aliased tables.. I'd write the code this way [except I probably wouldn't put in these specific comments]:

    select -- I prefer lowercase

    authors.Au_id, -- I prefer trailing commas

    authors.au_lname,

    authors.au_fname,

    titles.title,

    titles.title_id,

    titleauthor.royaltyper

    from titles

    left join titleauthor -- I use left, right, inner, or full outer before the join to keep my head straight

    on titles.title_id = titleauthor.title_id

    left join authors

    on titleauthor.au_id = authors.au_id

    I find it interesting that there has been no mention yet of the most common usage for table aliases that I see: Multiple references to the same table. Here's a simplified example:

    select

    loanapp.id,

    officer.name,

    processor.name,

    underwriter.name

    from loanapp

    left join contact officer

    on officer.id = loanapp.officer_id

    left join contact processor

    on processor.id = loanapp.processor_id

    left join contact underwriter

    on underwriter.id = loanapp.underwriter_id

    Does this just happen in my field or with the databases I have to work with?

  • I see your point, and use "proper grammar" rules for readability in most of my code, as in:

    Left(ColumnName, 1)

    instead of

    Left(ColumnName,1)

    This is just more natural to me.

    But at a certain point, that breaks down. Your example isn't SQL, so it's hard to judge SQL based upon it. I look at a select list much differently than parts of an address. I guess I use both leading and trailing commas depending upon situation. Trailing commas when the select list is simple (often w/o newlines for each column), and then at some point I'll switch to newlines and leading commas...especially when I'm not selecting:

    select

    column1,

    column2,

    column3

    from...

    but rather

    select

    , column1

    , case when i.complete = '1901-01-01 01:00:00.000' then 'P'

    when i.complete = '1901-01-01 02:00:00.000' then 'M'

    when i.complete > '1901-01-01 02:00:00.000' then 'C'

    else 'I' end

    as status

    , right('00' + cast(datepart(minute, isnull(s.datecommitted, z.lastupdate) - s.datecreated) as varchar), 2)

    + ':' + right('00' + cast(datepart(second, isnull(s.datecommitted, z.lastupdate) - s.datecreated) as varchar), 2)

    as tot_duration

    From...

    (BTW, I can't seem to control tabs well in the code blocks on the forum)

    The commas show my eye where each column to be returned starts.

  • Be careful. I always try to alias tables in my code and mark every column with those aliases, even if the column names are unique to the objects in the statement *at that time*. It has happened to me before that a column with the same name as another column in the statement gets added to one of the other tables in the statement, thus breaking the code with an ambiguous column name error.

  • My .02:

    I prefer option B

    1. It's readable. The aliases don't get into the way of the column, which is what I'm really interested in in the Select.

    2. I know where the columns are coming from. I just scan down to the from clause.

    3. Aliasing to a fully qualified schema speeds up compiling.

    If you're worried about clarity, why not do (something like) this?

    Select

    a.FirstName, --from Authors a

    a.LastName,

    a.AuthorID,

    t.Title, --from Titles t

    t.TitleID

    From dbo.Authors a

    inner join dbo.Titles t

    on a.AuthorID = t.AuthorID

  • Hooooot!

    Leading commas all the way!

    CAPS for all sql keywords!

    Keep the aliases short and sweet!

    😛

    I'd never have guessed the discussion would be this long, but it's a good read on a slow morning!

  • I've come across code that used synomyns. Since synomyns are not common for me, at first glance I was like "What? Where's that table coming from? It's not listed in the database! And there's no alias in the code!?!?!" But, I'd figured it out eventually and, looking through the code, when you are constantly using a table from another database, it does help on the readability of the code once you realize a synomym is being used. As I get more used to it, it won't throw me for a loop as often.

    The problem I have with table aliases is not having the habit ingrained of where to look for the actual table names. Since I'm looking at the code in sequence, the aliases used in the select statement (the a.col1, a.col2, b.col1, c. col3) sometimes get confusing because the alias is defined later on in the code (in the FROM statement - from dbo.table1 as a) . I think the use of "as" in the defining of the alias is helpful because that makes it easier to spot to then understand where everything in the select part is coming from.

    But then, I am also pretty new to the SQL scene, so I'll probably get better at spotting the aliases the more experience I get.

    As for the leading commas, it's a preference thing mostly, but since I was forcibly taught in school that the comma has to go directly AFTER the word, that there can be NO SPACES between the word and the comma, I tend to go with the trailing commas. I have no wish to have my grammar school teacher haunting me because I put the comma on the next line. :crazy:

    So, sorry all you leading comma guys, I'm with Jeff on this one. :w00t:

    -- Kit

  • Steve Jones - Editor (5/7/2009)


    For the record, I'm a leading commas guy, Jeff is wrong, and it's easier to read. All you trailing comma folk need to change!:-D:-D:w00t:

    BWAA-HAA!!! I was greasing up the ol' pork chop cannon for someone else, but I like big easy targets better. 😛 I'll be back...

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • tlehner (5/7/2009)


    Steve Jones - Editor (5/7/2009)


    For the record, I'm a leading commas guy, Jeff is wrong, and it's easier to read. All you trailing comma folk need to change!:-D:-D:w00t:

    However, I do think that in your group, you need to be consistent. I'd hate to upper case SQL, or go with trailing commas, but I would for consistency's sake.

    My preference is to camel case tables, go with 2-4 letter aliases that are meaningful (as in ALZDBA's example), and stick with lower case for most typing.

    +1. This is almost exactly how I code SQL.

    What's up w/ the leading comma haters? I suppose you put your ands and ors at the end of a line, too? lol...

    Tim

    Heh... what's up with all the leading comma lovers? Look at the email you just wrote... no leading commas. 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (5/7/2009)

    Heh... what's up with all the leading comma lovers? Look at the email you just wrote... no leading commas. 😉

    Lol...clever! I'll take it as a compliment that I'm able to switch between SQL and English so easily!

  • My Opinion

    , which is the way i always do it

    , is not to confuse the issue but rather

    : to simply use grammer as gramer is meant to be ie

    :- not like this reply

    , which is difficult to read and understand the flow

    .

    :

    )

  • Well

    , I think leading commands are

    , well

    , easier to read

    :hehe:

Viewing 15 posts - 46 through 60 (of 79 total)

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