|
|
|
2.5. Using AliasesColumn aliases and table aliases are temporary names assigned within a query to columns and tables respectively. They are created on the fly in a query, and do not exist after the query is run. In this section, we discuss column aliases and table aliases. 2.5.1. Column AliasesColumn aliases are used to improve the readability of a query and its output. In SQL Server , a column alias can be declared either before or after the column designation in the SELECT statement. We will first display a query without a column alias: SELECT dname, age, sex FROM Dependent WHERE age > 5 This query produces 17 rows of output (of which we show the first 10 rows): dname age sex -------------------- ------ ---- Matt 8 M Mary 9 F Beena 31 F Chris 6 M Tom 45 M James 14 M Hillary 16 F Phoebe 12 F Om 6 M Barbara 26 F . . . (17 row(s) affected) Notice that SQL Server (by default) uses the column names from the Dependent table for the column headings. These column names may not be so explicit or descriptive. For example, what is dname? We would probably assume it's a name of something, but what does the "d" in front of name stand for? Using more descriptive headings in the output would considerably increase readability. To use more descriptive column headings, you can include column aliases just before or after the column name by using AS in the SELECT statement, as shown next (in the first few examples, we place the descriptive column headings after the column names): SELECT dname AS Dependent_name, age AS Dependent_age, sex AS Dependent_sex FROM Dependent WHERE age > 5 This query produces 17 rows of output (of which we show the first 10 rows): Dependent_name Dependent_age Dependent_sex -------------------- ------------- ------------- Matt 8 M Mary 9 F Beena 31 F Chris 6 M Tom 45 M James 14 M Hillary 16 F Phoebe 12 F Om 6 M Barbara 26 F . . . (17 row(s) affected) That output has more descriptive headings. To embed a blank in the column alias, you have to put the column alias in single or double quotes, as shown in the following example: SELECT dname AS "Dependent Name", age AS "Dependent Age", sex AS "Dependent Sex" FROM Dependent WHERE age > 5 This query produces 17 rows of output (of which we show the first 10 rows): Dependent Name Dependent Age Dependent Sex -------------------- ------------- ------------- Matt 8 M Mary 9 F Beena 31 F Chris 6 M Tom 45 M James 14 M Hillary 16 F Phoebe 12 F Om 6 M Barbara 26 F . . . (17 row(s) affected) In fact, if you use single quotes in the previous query, you can also omit the AS. That is, typing in the following query gives you the same output as does the previous query: SELECT dname 'Dependent Name', age 'Dependent Age', sex 'Dependent Sex' FROM Dependent WHERE age > 5 Column aliases can also be placed in square brackets, as shown in the following query: SELECT dname AS [Dependent Name], age AS [Dependent Age], sex AS [Dependent Sex] FROM Dependent WHERE age > 5 Finally, column aliases can be placed in square brackets before = column name, as shown here: SELECT [Dependent Name] = dname, [Dependent Age] = age, [Dependent Sex] = sex FROM Dependent WHERE age > 5 These previous two queries produce the same output (and headings) as the query before them. If we wish to eliminate the brackets in the previous query, we can use only a one-word alias before the = column name, as shown: SELECT Name = dname, Age = age, Sex = sex FROM Dependent WHERE age > 5 This query produces 17 rows of output (of which we show the first 10 rows): Name Age Sex -------------------- ------ ---- Matt 8 M Mary 9 F Beena 31 F Chris 6 M Tom 45 M James 14 M Hillary 16 F Phoebe 12 F Om 6 M Barbara 26 F . . . (17 row(s) affected) 2.5.2. Table AliasesA table alias, usually used in multi-table queries (we discuss multi-table queries in Chapter 4 onwards), allows us to use a shorter name for a table when we reference the table in the query. A table alias is temporary, and does not exist after the query is run. We will explore multi-table queries in future chapters. Following is an example of the previous query written with a one-letter table alias: SELECT d.dname FROM Dependent d WHERE d.age > 5 This query produces 17 rows of output (of which we show the first 10 rows): dname -------------------- Matt Mary Beena Chris Tom James Hillary Phoebe Om Barbara . . . (17 row(s) affected) In this query, the table alias is the letter d after the table name, Dependent. A table alias can also be defined by a short, meaningful word or expression after the table name, rather than a one-letter table alias, but the one-letter table alias is commonly used by SQL programmers. Once a table alias has been defined in a query, it can be used in place of the table name. So, d could be used in place of Dependent if the table name needed to be used again in this particular query, but it is not reusable in multiple queries within the same batch. Again note that the table alias is not valid outside this query (or, after this query is executed). That is, if you type SELECT * from d, you will get an error message. There is no such table as d (d was locally defined as the table alias for that particular query, and is valid only in that particular query). 2.5.3. Table Aliases Used as Table QualifiersIn the previous example, the construction d.dname contains a table qualifier (the d. part). Table qualifiers are needed when the same column name has been used in more than one table. Table qualifiers before the column names determine which table the column is from. For example, if TableA has a column called Field1 and TableB also has a column Field1, if we do not use a table qualifier in a multi-table query, there is no way that the query engine can know which Field1 the query is referring to. To correctly handle this situation, we would have to use a table qualifier in the form Table1.FieldA, where Table1 is the table qualifier (this is also an alias, in a way).
Following is an example of a query with a table qualifier used for the age column: SELECT * FROM Dependent WHERE Dependent.age > 5 This query produces 17 rows of output (of which we show the first 10 rows): PNO DNAME RELATIONSHIP SEX AGE ------ -------------------- ------------ ---- ------ 2 Matt Son M 8 2 Mary Daughter F 9 2 Beena Spouse F 31 20 Chris Son M 6 62 Tom Husband M 45 62 James Son M 14 62 Hillary Daughter F 16 62 Phoebe Daughter F 12 126 Om Son M 6 128 Barbara Wife F 26 . . . (17 row(s) affected) It is also very common in SQL to alias a table and then also use the table alias as a table qualifier, as illustrated here: SELECT * FROM Dependent d WHERE d.age > 5 The output of this query will be the same as the output of the previous query. In this query, d (the table alias) is also the table qualifier. Not only is a construction like this very common, but it also helps to circumvent typing errors when writing commands. The advantages of using table qualifiers and table aliases may not be so apparent in the examples presented in this chapter, because we are working only with single tables here. As we start working with multiple tables (from Chapter 4 onwards), their advantages will become more obvious. |
|
|
|
