Inline views with MS SQL Server

Summary

  • Aliases are most often use to simplify somebody's life, either the programmer's or the end-user's.
  • What is clearer : VMT003 or Vertical Maintenance

Applies to:

  • Microsoft SQL Server 2000
  • Microsoft SQL Server 2005
  • Microsoft SQL Server 2008
  1. SELECT deptno, deptname AS department, mgrno AS manager
  2. FROM department
  3. WHERE manager >= '000050';
[Get Code]
Msg 207, Level 16, State 1, Line 1
Invalid column name 'manager'.

SQL Aliases columns are columns that use another name instead of the name of the column in the select statement.

Data used

  1. SELECT DEPTNO,DEPTNAME,isnull(MGRNO,'**')
  2. FROM department;
[Get Code]
DEPTNO DEPTNAME                             
------ ------------------------------------ ------
A00    SPIFFY COMPUTER SERVICE DIV.         000010
B01    PLANNING                             000020
C01    INFORMATION CENTER                   000030
D01    DEVELOPMENT CENTER                   **    
D11    MANUFACTURING SYSTEMS                000060
D21    ADMINISTRATION SYSTEMS               000070
E01    SUPPORT SERVICES                     000050
E11    OPERATIONS                           000090
E21    SOFTWARE SUPPORT                     000100

(9 row(s) affected)

Alias in column

Now let's retrieve the data with some friendlier names, the alias. SQL Aliases columns are columns that use another name that the name of the column in the select statement.

  1. SELECT DEPTNO,DEPTNAME,isnull(MGRNO,'**') AS Manager
  2. FROM department;
[Get Code]
DEPTNO DEPTNAME                             Manager
------ ------------------------------------ -------
A00    SPIFFY COMPUTER SERVICE DIV.         000010
B01    PLANNING                             000020
C01    INFORMATION CENTER                   000030
D01    DEVELOPMENT CENTER                   **    
D11    MANUFACTURING SYSTEMS                000060
D21    ADMINISTRATION SYSTEMS               000070
E01    SUPPORT SERVICES                     000050
E11    OPERATIONS                           000090
E21    SOFTWARE SUPPORT                     000100

(9 row(s) affected)

The title of the column says Manager, just like requested with the AS.

Alias in the Where column

It's easier to understand DEPARTMENT than DEPTNAME and MANAGER than MGRNO. Now the question is how to retrieve the information if the MANAGER is greater than 000050?

  1. SELECT DEPTNO,DEPTNAME,isnull(MGRNO,'**') AS Manager
  2. FROM department
  3. WHERE manager >= '000050';
[Get Code]
Msg 207, Level 16, State 1, Line 3
Invalid column name 'manager'.

It does not work because MANAGER does NOT exist yet when the WHERE clause is evaluated. SQL evaluates the statements in that order:

  1. the FROM clause
  2. the WHERE clause
  3. the SELECT clause

Inline Views

The MANAGER does not exist yet. The solution is to use an INLINE VIEW.

  1. SELECT * FROM (
  2. SELECT deptno, deptname AS department, isnull(mgrno,'**') AS manager
  3. FROM department) ABC
  4. WHERE manager >= '000050';
[Get Code]
deptno department                           manager
------ ------------------------------------ -------
D11    MANUFACTURING SYSTEMS                000060
D21    ADMINISTRATION SYSTEMS               000070
E01    SUPPORT SERVICES                     000050
E11    OPERATIONS                           000090
E21    SOFTWARE SUPPORT                     000100

(5 row(s) affected)
  • ABC is the name of the inline view.
  • The FROM clause is executed first, in this case the inline view, that returns the MANAGER, then it's possible to use the MANAGER in the WHERE clause.