Careful with table name alias

  • Creative statement good for braintraining but with a high theoretical fineness.

    Not any SQL developer with some common sense would name a table [where] nor would invent the alias [flag ='y' ]

  • There is nothing special about flag='y'... it's text. It could be anything He's just aliasing the table.

    The point is that by using square brackets you can alias something to even something crazy that looks like an expression.

    --These are all the same...

    Select * from [where] [flag ='y' ]

    Select * from [where] [Beer]

    Select [flag ='y' ].* from [where] [flag ='y' ]

    Select [Beer].* from [where] [Beer]

    Select table1.some_data from [where] table1

  • When I actually realized that

    [flag ='y' ]

    was an alias....I actually laughed. This is a situation so unlikely that it is humorous, but it teaches a good lesson. Great question for a Friday. Thanks.

  • Nice one.... also good to use as an interview question 😀

    _______________________________________________________________________
    For better assistance in answering your questions, click here[/url]

  • I love it! This ranks right up there with the Middle Join QOD, which is one of my all time favorites. I think someone actually tried to write a SQL Statement once that read as a poem using carefully named tables and a ton of table and column aliases, but I don't remember where I saw that now.

    This is great, nicely done Roshan!

    Chad

  • Richard M. (5/14/2010)


    Nice one.... also good to use as an interview question 😀

    Good point. Adding to the list of interview questions!

  • hmi (5/14/2010)


    Creative statement good for braintraining but with a high theoretical fineness.

    Not any SQL developer with some common sense would name a table [where] nor would invent the alias [flag ='y' ]

    I've seen stranger things.;-)

    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

  • The correct is 'Careful with ' not 'careful with '

  • Aside from the minor case correction of 'Careful with' that several astute observers noticed, this is a great question. It's even possible it will help in diagnosing something crazy like that showing up in legacy code. I know I have lots of room for improvement, but I would never actually write aliases like that or use [where] as a table name. But it is important to know that with the brackets, such statements would work.

    And I got the question right, to boot!

    Thanks,

    webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • Nice question, thank you. It would be nice though if the database engine team would limit some of the square brackets allowances and throw sev 20 error. Something like:

    Msg 8421, Level 20, State 1, Line 1

    Abuse of the database engine encountered.

    The connection has been forcibly closed by the server.

    Oleg

  • I think this was a good question, but the title "Careful with table name alias" sort of gave everything away.

  • Thanks for the question. Very creative, I would never have thought to write a question like this.

    ---------------------------------------------------------------------
    Use Full Links:
    KB Article from Microsoft on how to ask a question on a Forum

  • Very interesting question! The alias name puzzled me for a while.

    Amol Naik

  • So if you can alias without use of the AS clause why have it?

  • When using the AS to alias a Table, it makes it more clear to anybody coming after you to read what your intent was.

    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

Viewing 15 posts - 16 through 30 (of 45 total)

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