CODEWITHSUNDEEP
sqlsql-server-2005
ImGreg
ImGreg

Reputation: 2983

SQL Case Statement Proper Aliasing

I have a SQL Server 2005 query where I am using a case statement in the Select statement. I want to know what the "correct" way of doing it is.

Each format seems to work perfectly fine, but I was wondering if there is some redundancy between the different selects or something.

Sample formats:

SELECT 'isItemDisplayed' = CASE WHEN ... THEN ... END FROM myTable

SELECT isItemDisplayed = CASE WHEN ... THEN ... END FROM myTable

SELECT CASE WHEN ... THEN ... END AS isItemDisplayed FROM myTable

SELECT CASE WHEN ... THEN ... END AS 'isItemDisplayed' FROM myTable

SELECT CASE WHEN ... THEN ... END isItemDisplayed FROM myTable

Upvotes: 2

Views: 266

Answers (4)

Iłya Bursov
Iłya Bursov

Reputation: 24146

I suppose your question is about ANSI SQL standard, and peculiarities in Microsoft implementation

first, 1st = 2nd statement, and 3rd=4th=5th, but 1st != 3rd, as it compares, while the 3-5 returns value - UPDATE: for MS SQL all these queries are the same, for some other DB they are can be different

anyway, we can simplify your question to just to these two questions:

what is the proper way to escape identifiers

if you use identifier which cannot be mixed with reserved words - you do not need to enclose it with any special symbol, but if you're not sure - it is better to use them

standard SQL define " symbol as identifier delimiter, but MS SQL supports additional ones (like []), so you can use any of them, but " is preferable, check out http://en.wikibooks.org/wiki/SQL_Dialects_Reference/Data_structure_definition/Delimited_identifiers

should we use AS or not

keyword AS is optional in SQL standard, and its optional in MS implementation, but this cannot be true for all DBMS

check http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt - keyword [ AS ] - which means optional for this document

finally answer:

to be sure that your query always works as expected - use escaping and use AS keyword, this will help you in case of another DB or if some incompatible changes will be introduced in new versions of MS SQL

so, this query will be better:

SELECT CASE WHEN ... THEN ... END AS "isItemDisplayed" FROM "myTable"

Upvotes: 0

Elias
Elias

Reputation: 2632

Referencing the example at the bottom of this page, I would say:

SELECT CASE WHEN ... THEN ... END AS isItemDisplayed FROM myTable

because it explicitly defines isItemDisplayed as the name.

I personally would prefer:

SELECT CASE WHEN ... THEN ... END AS [isItemDisplayed] FROM myTable

because it covers for reserved words(even though you should never name anything the same as a reserved word) and you can include spaces.

According to this blog, not using any explicit identifier(= Or As) is poor practice.

There is no advantage to using single quotation marks around an alias, besides the SSMS showing the name as a different color, maybe making it stick out?

Upvotes: 3

Joel Coehoorn
Joel Coehoorn

Reputation: 415971

This really has nothing to do with the CASE statement. You can create a column alias for any expression in the select list, including simple column names (ie: to re-name to match client code), literal expressions, function calls, etc. But continuing with your CASE example, there are some things to know.

I almost never see it this way, but the ANSI Sql standard says to use double quotes for the name, like so:

SELECT CASE WHEN ... THEN ... END "isItemDisplayed" FROM myTable

What I do commonly see are the 3rd or 5th options from your original question, shown below. Either is fine:

SELECT CASE WHEN ... THEN ... END As isItemDisplayed FROM myTable

SELECT CASE WHEN ... THEN ... END isItemDisplayed FROM myTable

Either of these also allow you to encase the names in square-brackets ([]) if you want a name that conflicts with a reserved word or uses spaces. I would avoid any that use the = syntax, as it could confuse someone that you're looking for a boolean result. The other bit advice I can give here is to pick one style and stick with it in a current environment. Consistency!

Upvotes: 2

Rahul Tripathi
Rahul Tripathi

Reputation: 172488

I think this is the one which is preferred:-

SELECT CASE WHEN ... THEN ... END AS isItemDisplayed FROM myTable

The first two are used to compare the results of the CASE statements against the name. And the last to results which we are assigning as the ALIAS.

Just found this link interesting(As mentioned by Lamak in comments):

Personally I find the single quotes around aliases to be distracting, making the select list tougher to read no matter which aliasing convention you use. All alias names are strings; why would I want to make it look like it is intended to be data? Also note that #5 and #6 are not defined in any standard, they just seem to be allowed into the syntax

Upvotes: 0

Related Questions